VLOOKUP Problem

[engelwood]

I've had this problem in the past but have never had time to try and figure it out. While doing a VLOOKUP I get all of my returns to come back "#N/A." However when I look on the sheet I'm referencing they are actually listed. If I click the cell and press enter the VLOOKUP will then return the reference. Does anyone know why this happens?

 

[Aladin Akyurek]

I've had this problem in the past but have never had time to try and figure it out. While doing a VLOOKUP I get all of my returns to come back "#N/A." However when I look on the sheet I'm referencing they are actually listed. If I click the cell and press enter the VLOOKUP will then return the reference. Does anyone know why this happens?

There is a mismatch between the data type of the lookup value and the data type of the values in the first column of your lookup table. That is, something like:

text vs numeric

or

numeric vs text

Conclusion: You need to align the data types, that is, make them of the same type.

 

[KenWright]

One easy way of doingf what Aladin has suggested is to put a 1 in a cell, copy the cell, select your range of data and do Edit / Paste Special / Multiply. This should coerce textual numbers back to real numbers.

 

[engelwood]

That's what I thought at first but, both worksheets are on general format.

 

[shayb]

i always make a new column and then trim the numbers.

=trim(A1)

then copy/paste special values and delete the 1st column.

 

[Seti]

Try =ISTEXT(cell) for both sets of values to see if they are text or numeric. Just because a format says general doesn't mean the data id actually numeric.

 

[engelwood]

Thanks for the suggestions!