Excel VLOOKUP not matching values that only contain numbers

Question

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?

Answer 1

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)