Using VLOOKUP for SKUs stored as text

[Brandon_1]

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:

[#2]

@Brandon_1

Hi,

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

[#3]

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

[#4]

I added an example above @Haytham Amairah 

[#5]

@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

[#6]

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