Forum Discussion
VLOOKUP with text and numbers in same cell
Please attach a sample workbook demonstrating the problem, or make it available through OneDrive, Google Drive, Dropbox or similar.
- https://discounttire-my.sharepoint.com/:x:/p/martin_dorantes/EV0h_l5wsFtLmbsiYUoxi_oBRkEX8E3JBmTy8OrdjW8Lxw?e=70lEkW
Your VLOOKUP isn't working because it looks at the first column, but what you're asking it to look at is the second (where Store ID resides).
You'd be better served with INDEX and MATCH. as in the attached.
- This worked! thank you so much
Thanks! VLOOKUP always looks at the first column of the lookup range. But the store is in the second column of the StoreList table.
You can use
=VLOOKUP([@Store],StoreList[[Store]:[Region]],2,FALSE)
or better
=IFERROR(VLOOKUP([@Store],StoreList[[Store]:[Region]],2,FALSE),"")
to suppress #N/A in the rows where no store number has been entered yet.
If you have Microsoft 365 or Office 2021, you can also use
=XLOOKUP([@Store],StoreList[Store],StoreList[Region],"")
This works nicely for the ZIP code too:
=XLOOKUP([@Store],StoreList[Store],StoreList[Zip],"")
In older versions, you have to use INDEX/MATCH for the Zip code:
=IFERROR(INDEX(StoreList[Zip],MATCH([@Store],StoreList[Store],0)),"")
