Forum Discussion
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 |
Please help.. 😞
Thank you!
=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 Replies
=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.
- nvm I changed one little thing and it works! Super long formula but it did the job. Thank you!!!!!
- 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?
