New Contributor

# 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:

5 Replies

# Re: Using VLOOKUP for SKUs stored as text

@Brandon_1

Hi,

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

# Re: Using VLOOKUP for SKUs stored as text

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

# Re: Using VLOOKUP for SKUs stored as text

I added an example above @Haytham Amairah

# Re: Using VLOOKUP for SKUs stored as text

@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

# Re: Using VLOOKUP for SKUs stored as text

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