# Excel Question - VLookup with a partial match **URL:** \ **Category:** Factual Questions\ **Created:** [December 16, 2021, 11:18pm UTC](https://boards.straightdope.com/t/excel-question-vlookup-with-a-partial-match/956186 "2021-12-16T23:18:47Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![StGermain](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/stgermain/32/2868_2.png) [@StGermain](https://boards.straightdope.com/u/StGermain)\ **Post date:** [December 16, 2021, 11:18pm UTC](https://boards.straightdope.com/t/excel-question-vlookup-with-a-partial-match/956186/1 "2021-12-16T23:18:47Z")
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. StG ---
**Author:** ![penultima\_thule](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/penultima_thule/32/3833_2.png) [@penultima\_thule](https://boards.straightdope.com/u/penultima_thule)\ **Post date:** [December 17, 2021, 3:37am UTC](https://boards.straightdope.com/t/excel-question-vlookup-with-a-partial-match/956186/2 "2021-12-17T03:37:42Z")
You can include wildcards in the vlookup string formula =vlookup(“_”&string&”_”,range,column,logical) Exceljet is a good reference: [https://exceljet.net/formula/partial-match-with-vlookup](https://exceljet.net/formula/partial-match-with-vlookup) ---
**Author:** ![StGermain](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/stgermain/32/2868_2.png) [@StGermain](https://boards.straightdope.com/u/StGermain)\ **Post date:** [December 17, 2021, 4:13am UTC](https://boards.straightdope.com/t/excel-question-vlookup-with-a-partial-match/956186/3 "2021-12-17T04:13:29Z")
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. Thanks, though. StG ---
**Author:** ![penultima\_thule](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/penultima_thule/32/3833_2.png) [@penultima\_thule](https://boards.straightdope.com/u/penultima_thule)\ **Post date:** [December 17, 2021, 4:33am UTC](https://boards.straightdope.com/t/excel-question-vlookup-with-a-partial-match/956186/4 "2021-12-17T04:33:11Z")
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? ---
**Author:** ![Magiver](https://avatars.discourse-cdn.com/v4/letter/m/4491bb/32.png) [@Magiver](https://boards.straightdope.com/u/Magiver)\ **Post date:** [December 17, 2021, 4:43am UTC](https://boards.straightdope.com/t/excel-question-vlookup-with-a-partial-match/956186/5 "2021-12-17T04:43:53Z")
could you use the original formula by flipping the lookup and range and then pivot the results ---
**Author:** ![penultima\_thule](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/penultima_thule/32/3833_2.png) [@penultima\_thule](https://boards.straightdope.com/u/penultima_thule)\ **Post date:** [December 17, 2021, 4:46am UTC](https://boards.straightdope.com/t/excel-question-vlookup-with-a-partial-match/956186/6 "2021-12-17T04:46:36Z")
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. [https://exceljet.net/formula/extract-all-partial-matches](https://exceljet.net/formula/extract-all-partial-matches) ---
**Author:** ![StGermain](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/stgermain/32/2868_2.png) [@StGermain](https://boards.straightdope.com/u/StGermain)\ **Post date:** [December 17, 2021, 4:49am UTC](https://boards.straightdope.com/t/excel-question-vlookup-with-a-partial-match/956186/7 "2021-12-17T04:49:49Z")
I’ll play with it tomorrow, thanks! **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. StG ---
**Author:** ![bump](https://avatars.discourse-cdn.com/v4/letter/b/7c8e57/32.png) [@bump](https://boards.straightdope.com/u/bump)\ **Post date:** [December 17, 2021, 2:19pm UTC](https://boards.straightdope.com/t/excel-question-vlookup-with-a-partial-match/956186/8 "2021-12-17T14:19:54Z")
Maybe posting 10-15 sample data elements might be helpful? ---
**Author:** ![StGermain](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/stgermain/32/2868_2.png) [@StGermain](https://boards.straightdope.com/u/StGermain)\ **Post date:** [December 17, 2021, 2:40pm UTC](https://boards.straightdope.com/t/excel-question-vlookup-with-a-partial-match/956186/9 "2021-12-17T14:40:12Z")
How can I post a couple rows of a spreadsheet? StG ---
**Author:** ![StGermain](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/stgermain/32/2868_2.png) [@StGermain](https://boards.straightdope.com/u/StGermain)\ **Post date:** [December 17, 2021, 3:35pm UTC](https://boards.straightdope.com/t/excel-question-vlookup-with-a-partial-match/956186/10 "2021-12-17T15:35:14Z")
**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 ---
**Author:** ![StGermain](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/stgermain/32/2868_2.png) [@StGermain](https://boards.straightdope.com/u/StGermain)\ **Post date:** [December 17, 2021, 3:35pm UTC](https://boards.straightdope.com/t/excel-question-vlookup-with-a-partial-match/956186/11 "2021-12-17T15:35:58Z")
**Table Array** GAMASTAN HEPAGAM HYPERHEP HYPERRAB HYPERRHO HYPERTET KEDRAB MICRHOGAM NABI RHOPHYLAC VARIZIG WINRHO CUTAQUIG FLEBOGAMMA GAMMAGARD GAMMAKED GAMMAPLEX GAMUNEX-C HIZENTRA HYPERHEP HYQVIA NABI-HB OCTAGAM PANZYGA PRIVIGEN WINRHO XEMBIFY ARTISS COSEAL DUPLOCATH ---
**Author:** ![StGermain](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/stgermain/32/2868_2.png) [@StGermain](https://boards.straightdope.com/u/StGermain)\ **Post date:** [December 17, 2021, 3:36pm UTC](https://boards.straightdope.com/t/excel-question-vlookup-with-a-partial-match/956186/12 "2021-12-17T15:36:43Z")
| Lookup Value | | Table Array | | --- | --- | --- | | HYPERRAB SF 300U/ML 5ML | | GAMASTAN | | GAMASTAN SF 15-18% 2ML | | HEPAGAM | | GAMASTAN SF 15-18% 10ML | | HYPERHEP | | THROMBATE III PR 0602U WHS | | HYPERRAB | | THROMBATE III PR 0569U WHS | | HYPERRHO | | HYPERRAB S/D SF 150U/ML2ML WHS | | HYPERTET | | HYPERRAB S/D SF150UML10ML WHS | | KEDRAB | | HYPERRHO S/D SY 300MCG PF WHS | | MICRHOGAM | | HYPERRHO S/D SY300MCG 10 WHS | | NABI | | HYPERRHO S/D SY 300MCG 10 PF | | RHOPHYLAC | | HYPERTET S/D SY 250U 1ML WHS | | VARIZIG | | GAMASTAN S/D SF 15-18%2ML WHS | | WINRHO | | GAMASTAN S/D SF15-18%10ML WHS | | CUTAQUIG | | HYPERHEP B SF 1ML | | FLEBOGAMMA | | HYPERHEP B S/D SY 1ML PF WHS | | GAMMAGARD | | HYPERHEP B S/D SY 0.5ML WHS | | GAMMAKED | | HYPERHEP B S/D SF 5ML WHS | | GAMMAPLEX | | HYPERRHO S/D SY 10 MDOSE WHS | | GAMUNEX-C | | GAMUNEX-C SF 10% 1GM 10ML WHS | | HIZENTRA | | GAMUNEX-C SF 10%2.5GM25ML WHS | | HYPERHEP | | GAMUNEX-C SF 10% 5GM 50ML WHS | | HYQVIA | | | | NABI-HB | | | | OCTAGAM | | | | PANZYGA | | | | PRIVIGEN | | | | WINRHO | | | | XEMBIFY | | | | ARTISS | | | | COSEAL | | | | DUPLOCATH | ---
**Author:** ![md-2000](https://avatars.discourse-cdn.com/v4/letter/m/9d8465/32.png) [@md-2000](https://boards.straightdope.com/u/md-2000)\ **Post date:** [December 17, 2021, 5:38pm UTC](https://boards.straightdope.com/t/excel-question-vlookup-with-a-partial-match/956186/13 "2021-12-17T17:38:37Z")
Is it always the case the lookup value that matches is the first word up to the space like the examples? Or are there cases like where we still look for SMITH if it’s “John Smithson Jr.”? I assume you’re looking for an exact match to the table array value? ---
**Author:** ![StGermain](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/stgermain/32/2868_2.png) [@StGermain](https://boards.straightdope.com/u/StGermain)\ **Post date:** [December 17, 2021, 8:12pm UTC](https://boards.straightdope.com/t/excel-question-vlookup-with-a-partial-match/956186/14 "2021-12-17T20:12:48Z")
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. StG ---
**Author:** ![bump](https://avatars.discourse-cdn.com/v4/letter/b/7c8e57/32.png) [@bump](https://boards.straightdope.com/u/bump)\ **Post date:** [December 17, 2021, 8:16pm UTC](https://boards.straightdope.com/t/excel-question-vlookup-with-a-partial-match/956186/15 "2021-12-17T20:16:07Z")
That’s what I was wondering too. Seems like you’re going to need to parse out that first word and then do your lookup based on that.