Forum Discussion

mdorantes's avatar
mdorantes
Copper Contributor
Apr 20, 2022
Solved

VLOOKUP with text and numbers in same cell

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!

excel
Excel for web
Formulas and Functions
  • mathetes's avatar
    mathetes
    Apr 20, 2022

    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.

5 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Apr 20, 2022

    mdorantes 

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

    • mdorantes's avatar
      mdorantes
      Copper Contributor
      Apr 20, 2022
      https://discounttire-my.sharepoint.com/:x:/p/martin_dorantes/EV0h_l5wsFtLmbsiYUoxi_oBRkEX8E3JBmTy8OrdjW8Lxw?e=70lEkW
      • mathetes's avatar
        mathetes
        Silver Contributor
        Apr 20, 2022

        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.

Resources