Multiple VLOOKUPs with a MATCH and ISNA component (or not)

**Sandinista** · 01-31-2012, 04:07 PM

Hi all, I have been racking my brain on this one for a very long time and all with no results.

My problem is this: I would like to match the data (or a specific word within that cell) to one of two tables. Specifically, I have

A B
1 Business NAICS
2 Pharmacy on Main
3 Doug's Pharmacy
4 Corner Convenience
5 Convenience Supercenter
6 Vacant

While a VLOOKUP will match specific stores with their respective NAICS codes using the VLOOKUP I already have, ie:

D E
Business Match NAICS
1 Vacant 9999
2 Convenience Supercentre 44512

But I would like to make it easier for all the times I have stores whose names indicate what they are as is often the case with florists, pharmacists and others, so I would like to compile another table like:

G H
1 Name Match NAICS
2 Pharmacy 44611
3 Convenience 44512

Key to this is that the formula will have to recognize that somewhere in the name is one of the words from the second table, and with that being so, to take the value of the cell adjacent to that matched word.

I promise you this is a brain teaser, and one which I have not made much head way (but found a lot out about excel in the process).

As I can tell, it will have something to do with =IF(ISNA(. . . . .),VLOOKUP(A2,D1:E2,2,FALSE) function so that if I do not have the exact business match in my list of business match, then excel will recognize the #N/A and then perform the alternative match. I am totally lost on this one, so any help is greatly appreciated, and to be sure you'd have my utmost respect as an excel wizard if you can actually get this one!

**rylo** · 01-31-2012, 05:57 PM

Hi

Add an example workbook and show the expected results for the items you have nominated.

rylo

**Sandinista** · 02-01-2012, 09:35 AM

Thanks, I should have thought to do that instead of attempting to outline the problem through html in the text of the post. Anyways, I have updated the post to include the xls file - best of luck, and thanks for having a look at it.

**CANAPONE** · 02-01-2012, 09:45 AM

Hi, in b3 and down:

=IFERROR(VLOOKUP(A3,$G$3:$H$4,2,0),INDEX($E$3:$E$4,MATCH(1,SEARCH("*"&$D$3:$D$4&"*",A3))))

to be confirmed with control+shift+enter

Edit: Excel 2003 formula (see also the file attached)

=IF(ISNA(VLOOKUP(A3,$G$3:$H$4,2,0)),INDEX($E$3:$E$4,MATCH(1,SEARCH("*"&$D$3:$D$4&"*",A3))),VLOOKUP(A3,$G$3:$H$4,2,0))

again, to be confirmed with control+shift+enter before to be copied down.

Regards

**Sandinista** · 02-01-2012, 09:53 AM

THANK YOU SO MUCH CANAPONE!!! Works like a charm. In trying to solve this myself I had come to realize that it would not necessarily be 2 VLOOKUPS, but rather to use an INDEX, MATCH, SEARCH combination for one - but was always met with brick walls. So thank you again for figuring this one out!