SOLVED

LOOKUP Formula, but not 100% exact terms?

Brass Contributor

I have two tabs, both with similar information, just formatted differently. I need to add the region to the first tab that correlates with the location, location name, or store name. The location name and store name are not usually similar in terms of how it is spelled out.

 

I would assume the easiest way to find the region would be to match the location ID (tab 1) to the store number (tab 2). The only problem is there are extra characters, i.e. extra zeros in the location ID.

 

RegionLocation IDLocation Name
 0079Store XXX #79

 

RegionStore NumberCity, State
Columbia79Columbia, SC
Columbia82xxx
Atlanta22xxx

 

 

Please help.. :(

 

Thank you!

3 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@kittenmeants 

=IFERROR(INDEX($A$8:$A$10,MATCH(TRUE,ISNUMBER(SEARCH(SUBSTITUTE(B2,0,""),$B$8:$B$10)),0)),"")

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.

index match.png

I tried this formula and it just comes back with no entries. It doesn't populate an error either, just no data. Not sure if the problem is becuase I am switching between tabs?
nvm I changed one little thing and it works! Super long formula but it did the job. Thank you!!!!!
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@kittenmeants 

=IFERROR(INDEX($A$8:$A$10,MATCH(TRUE,ISNUMBER(SEARCH(SUBSTITUTE(B2,0,""),$B$8:$B$10)),0)),"")

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.

index match.png

View solution in original post