Using VLOOKUP for SKUs stored as text

New Contributor

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:

5 Replies

@Brandon_1

 

Hi,

 

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

Perhaps, you can use 0 as your SKU, custom format it as 0000, then use 0 as your lookup_value argument in VLOOKUP.

I added an example above @Haytham Amairah 

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

INDEX & MATCH.png

 

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

@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!