Assuming you are not finding leading or trailing spaces and the information
being looked up is numeric in nature then I would suggest that the issue is
most likely text and numbers. If cells with numeric data are being stored as
text just chaning the cell format does not change the underlying text to
numbers. You still need to convert the text to numbers. Easiest wasy is to
place a 1 in an unused cell Copy it and paste special | Multiply over the
range of numbers. To catch this error in your vlookup you can use countif to
distinguish text from numbers. Countif treats everything as text so it will
find matches when one cell is text and the other number.

=if(countif(A1:A10, D1) = 0, "Not Found", vlookup(D1, A1:B10, 2, 0))

If countif finds a match then it proceeds to the vlookup. If the vlookup
returns an error then you have a data type mismatch between text and number.
--
HTH...

Jim Thomlinson


"Maki" wrote:

Hi all,

What's the cause of incidents when data in two cells, looking exactly the
same, are not recognised by Excel as the same?

It happens quite often when:
- vlookup function doesn't return any value even though the lookup_value
exist and looking exactly the same in lookup table;
- the same data appears multiple times when you do advanced filtering and
check "unique records only" checkbox; etc. etc.

I've looked at usual suspects, like numbers entered as text?, space at the
end of the record?, so on so forth, but, believe it or not, sometimes, these
things are exactly the same, at least to my naked eyes, and still Excel seems
to think they are different.

What other causes can there be in cases like this?

Thanks for your help.

Cheers,
--
Maki @ Canberra.AU