Odd VLOOKUP failure

[cannibalflea]

Hi all,

I am trying to lookup some values from a table on a different sheet. I've used VLOOKUP before and never had any issues with it. But now, for some of the lookup values, I get a N/A error. I've checked to make sure that the numbers are indeed numbers: 1) by recreating both lookup values and the table values on a different column and pasting the values 2) by multiplying the values by 1.

I just don't get it. I'm beginning to suspect a bug in Excel which if it exists would be very catastrophic. Am I missing something or is there something wrong with excel or my workbook?

Regards,
Ali

[etaf]

i have done a text to columns on the lookup values as for some reason they are not seen correctly

if you click on a value in column A Contours - it then works
so click on column A
data> text to columns > delimited>next>next>leave on general format and finish

now most will work
0.08
on the table is
0.0800000000000001
so fails

and for 0.35
if i do a round (=ROUND(B53*$D$11,2) in C53 it works OK - so I suspect a decimal place issue

you may want to round all the values

see attached sheet

[cannibalflea]

Thanks Wayne. I used the text to column method but some of the numbers had a 000000000000001 at the end which through everything off. So I then rounded the numbers and then copy-paste the values over top. Now it works as it should. I'm still not sure how it happened, but I'm glad it worked.

[Pepe Le Mokko]

The reason for the 000000000001 is explained here

[etaf]

your welcome