VLOOKUP with text and numbers in same cell

[mdorantes]

My data contains store numbers in the form "ABC 123" I am trying to vlookup the store number to reference the region the store is in.

My formula now is: =VLOOKUP([@Store],StoreList,1,FALSE)

Region is in column 1. This has worked before when I looked up the zip code (12345) but does not work when looking up the actual store number (ABC 123)

How can vlookup work with both text and numbers in the same cell? Thanks for the help in advance!

[#2]

@mdorantes 

Please attach a sample workbook demonstrating the problem, or make it available through OneDrive, Google Drive, Dropbox or similar.

[#3]

https://discounttire-my.sharepoint.com/:x:/p/martin_dorantes/EV0h_l5wsFtLmbsiYUoxi_oBRkEX8E3JBmTy8Or...

[#4]

@mdorantes 

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)),"")

[#5 · Accepted solution]

@mdorantes 

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.

[#6]

This worked! thank you so much