SOLVED

# LOOKUP Formula, but not 100% exact terms?

Brass Contributor

# LOOKUP Formula, but not 100% exact terms?

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.

 Region Location ID Location Name 0079 Store XXX #79

 Region Store Number City, State Columbia 79 Columbia, SC Columbia 82 xxx Atlanta 22 xxx

Thank you!

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

# Re: LOOKUP Formula, but not 100% exact terms?

``=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.

# Re: LOOKUP Formula, but not 100% exact terms?

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?

# Re: LOOKUP Formula, but not 100% exact terms?

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

# Re: LOOKUP Formula, but not 100% exact terms?

``=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.