Not sure why this excel vlookup isn't working.

[fuzzybabybunny]

Worksheet 1:

Column A has 50 unique item SKUs
Column B is empty - I want to populate this with prices from Worksheet 2.

Worksheet 2:

Column A has 4000 unique item SKUs including the 50 in worksheet 1.
Column B has the associated prices for these SKUs.

So in worksheet 1 Column B for the first SKU I type:

=vlookup(A1,'Worksheet 2'!A:B,2,FALSE)

Simple, right?

SKUs that are a mix of letters and numbers of the form 123456TL are looked up correctly and the prices are matched.

SKUs that are all numbers 123456 return a "Value Not Available" error.

I've already converted both SKU columns in both worksheets to the Text data type. Same error occurs if I convert all SKUs to the General data type.

What am I missing?

EDIT:

Same error when I input the values directly into the formula:

=vlookup(123456,'Worksheet 2'!A:B,2,FALSE)

But this works:

=vlookup("123456TL",'Worksheet 2'!A:B,2,FALSE)

 

[fuzzybabybunny]

I think I figured out what was wrong. The SKUs in Worksheet 2 were stored as text. I converted them to numbers and now everything works. But I have no idea why having the SKUs stored as text is a bad thing - they ARE text, aren't they? Something like 123456TL should be text, not a number. Performing mathematical operations like 123456TL * 2 is nonsensical.

 

[KIAman]

Hmm, I've never had vlookup issues regarding data types. Although, I also never define the table array as the entire worksheet.

Try naming the table array exactly the data you want to reference, for example SKUPRICES
In worksheet 1, I'm assuming the SKU is in column 1 with a header row.

Then try =VLOOKUP($A2, SKUPRICES ,2 ,FALSE) then copy and paste this line down to the end of the SKU.