I can do a VLookup where the lookup value contains wildcards to pull matches from the table array. So you could use &*"Smith"*& for the lookup value and match to John Smith Jr. But how can I do a lookup where the lookup value is John Smith Jr and your table array contains Smith?
In my case I work for a distribution company. We will have item descriptions that contain the brand and size (ex: Rice Krispies 14 oz) and I have a list of brands. I want to find all the values where the brands in the list are contained in the item description.
This would’ve been a simple matter 6 months ago. Thank you, SAP conversion.
Yeah, but I need it in the range, not the lookup value part of the formula. I used pretty much the same formula in my sample above.
But what I need is to have the wildcard in the middle of the formula. So using my cereal example above, my lookup value might be “Raisin Bran Crunch with Nuts, 15 oz”. My table array contains “Raisin Bran”. I need find all the Raisin Bran varieties and sizes in my list.
OK, so what you want is effectively the filtered list.
Will get back to you. Will probably need to use MATCH and some helper columns.
Roughly how many matches are you expecting. A handful, a dozen, hundreds?
Right, so I reckon I can get it to work with “standard functions” like MATCH, INDEX, COUNTIF and some helper columns but it would be nowhere near as elegant as this solution.
Magiver - I have a list of branded products, but not all our products. I only want to find the items whose brand names are on the list. Possibly 20K items, 350 brands.
Lookup Value
HYPERRAB SF 300U/ML 5ML
GAMASTAN SF 15-18% 2ML
GAMASTAN SF 15-18% 10ML
THROMBATE III PR 0602U WHS
THROMBATE III PR 0569U WHS
HYPERRAB S/D SF 150U/ML2ML WHS
HYPERRAB S/D SF150UML10ML WHS
HYPERRHO S/D SY 300MCG PF WHS
HYPERRHO S/D SY300MCG 10 WHS
HYPERRHO S/D SY 300MCG 10 PF
HYPERTET S/D SY 250U 1ML WHS
GAMASTAN S/D SF 15-18%2ML WHS
GAMASTAN S/D SF15-18%10ML WHS
HYPERHEP B SF 1ML
HYPERHEP B S/D SY 1ML PF WHS
HYPERHEP B S/D SY 0.5ML WHS
HYPERHEP B S/D SF 5ML WHS
HYPERRHO S/D SY 10 MDOSE WHS
GAMUNEX-C SF 10% 1GM 10ML WHS
GAMUNEX-C SF 10%2.5GM25ML WHS
GAMUNEX-C SF 10% 5GM 50ML WHS
I’d say the first word is an exact match 99% of the time. With pharma, sometimes abbreviations are used. But since this is just a reference tool, I could so a text-to-columns and look it up on the first word. Or a fuzzy lookup, but I never have much luck with fuzzy lookup.