Discussion Locked

Vlookup ~ formatng

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I make quite extensive use of Vlookup & Pivot tables. Sometimes I run into problems when the LOOKUP cell and the table are not formated (i think)the same.
To clarify, I get a 'n/a' response in the formula, but when i copy and and paste the lookup cell to the table (or the other way around), the formula work. I have tried formating the lookup column the same as the table column i.e. text or number but it does not seen to work. This gives me a headache specially when the lookup cell is within a pivot table.
suggestions pls !!!!!

3 replies

by amykong30

Not sure if is the problem that you are having, but the lookup value needs to be in the first column of your table array.

Could you rephrase your question? Thanks.

by rafiki

The lookup formula's are working OK.
If the lookup value is '123ABC' and the value in the lookup table (column 1) contains '123ABC', I still get 'n/a' i.e. no value found.
But if copy the lookup value and paste it in the lookup table the formula works & returns the right value.
Is this due to how the lookup cell & the lookup table are formated ? If so, how do i correct this without copy & paste route.

by choppit

If you can't see a difference between the two values but excel insists there is no match then the difference must be invisible.........

Check your lookup column text doesn't have any trailing spaces.

This discussion is closed to new replies.