LOOKUP Formula, but not 100% exact terms?

[kittenmeants]

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

Please help.. :(

Thank you!

[#2 · Accepted 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.

[#3]

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?

[#4]

nvm I changed one little thing and it works! Super long formula but it did the job. Thank you!!!!!