Excel VLOOKUP not matching values that only contain numbers

3
votes

This question is in reference to the following answer: How can I sort one set of data to match another set of data in Excel?

=VLOOKUP(A2, Sheet2!A:B, 2, 0)

I am attempting to use this formula to match up two sets of data exactly as the example shows. The formula is working perfectly for matching values that contain a letter within the cell. However, the formula is not matching up values that contain only numbers within the cell. What would be the cause of this? Any ideas?

2
excelexcel-formulavlookup

2 Answers

5
votes

This is most likely because one of the two lists has numbers formatted as text. If it's the list for which you are filling out values using this formula then you could use:

=VLOOKUP(0+A2, Sheet2!A:B, 2, 0)

Which will force A2 to be a number, so it is looked up properly in Sheet2!A:B.

If, however, your Sheet2!A column has numbers formatted as text, then you can use:

=VLOOKUP(TEXT(A2, "#"), Sheet2!A:B, 2, 0)
0
votes

When using VLOOKUP the "data type" of the lookup value and the first column of the table array needs to match - you probably have a table array formatted as text - does this work?

=VLOOKUP(A2&"", Sheet2!A:B, 2, 0)

concatenating a "null string" to the lookup value converts it to text