I simply need to compare two columns in

excel and this is the formula am using:

=VLOOKUP(A53,'Full

List'!A$2:Q$93,17,FALSE)I noticed that it finds text data but not

numbers.Can someone please help?

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.

Hi, 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:

=VLOOKUP(VALUE(A2),D3:E8,2,FALSE)

or number to text:

=VLOOKUP(TEXT(A2,"0"),D3:E8,2,FALSE)

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.

Regards

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History