Forum Discussion

Brandon_1's avatar
Brandon_1
Copper Contributor
Jun 28, 2019

Using VLOOKUP for SKUs stored as text

Hi there,

 

I'm using vlookup and it's returning #n/a. I used =type() and found it's type 2 which is text, which is why it isn't working. However, when I convert text to number I go from "0000" to just 0, which, as a SKU, I can't allow. How do I keep "0000" as text but still use vlookup? Thank you!

 

Here's an example data set:

excel

5 Replies

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Jun 28, 2019

    Brandon_1

     

    In this case, you have two options:

    • The recommended one: is to unify the datatype of the SKU in both tables.
    • The other is to use INDEX & MATCH instead of VLOOKUP as follows:
    =INDEX('Example Base Data'!$B$2:$B$5,INDEX(MATCH(--A2,--'Example Base Data'!$A$2:$A$5,0),))

     

    You will notice the double negative sign (--) used in MATCH function, it's used to convert the numbers that are stored as texts to numeric numbers.

     

    Hope that helps

    • Brandon_1's avatar
      Brandon_1
      Copper Contributor
      Jun 28, 2019

      Haytham Amairah Hi, it seems to work in that sheet. I can't get it working in my data but I'll keep messing around with it and let you know what I find. I appreciate the help!

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Jun 28, 2019
    Perhaps, you can use 0 as your SKU, custom format it as 0000, then use 0 as your lookup_value argument in VLOOKUP.
  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Jun 28, 2019

    Brandon_1

     

    Hi,

     

    Could you provide us with a sample of the data you working on (including the VLOOKUP formula) as an attached spreadsheet?

Resources