Vlookup does not return numbers

June 30, 2010 at 20:41:37
Specs: Windows XP
I simply need to compare two columns in
excel and this is the formula am using:

I noticed that it finds text data but not

Can someone please help?

See More: Vlookup does not return numbers

July 1, 2010 at 04:13:13
Although I can't see your spreadsheet from where I'm sitting, my guess is that all of the data in Full List!A2:A93 is being seen as text.

Try this:

Select an empty cell and Copy it.

Select Full List!A2:A93 do an Edit...PasteSpecial...Add

This should change "numbers formatted as text" to numbers.

If that doesn't work, you might have leading/trailing spaces in the cells with the "numbers" which will need to be fixed with TRIM() and/or CLEAN() and turned back into numbers.

Report •

July 1, 2010 at 04:13:29

VLOOKUP() works for both numbers and text.

It is likely that your problem is because either the value being searched for or the values in the lookup table are not the same - both look the same, but one is a real number while the other is text.

To see which is which, format the cells with the 'General' number format. Text then appears left aligned and numbers appear right aligned,
or try applying a decimal number format. Numbers will be displayed as 1.00 whilst text will remain as 1.

If this is the problem, you can change text to numbers in a formula:
or number to text:
where the "0" is a format, in this case whole numbers with no decimal places and no thousands separator.

Alternatively, ensure that the stored data is consistent - preferably text that looks like a number should be stored as numbers.


Report •
Related Solutions

Ask Question