General discussion


Vlookup ~ formatng

By Rafiki ·
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 !!!!!

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by amykong30 In reply to Vlookup ~ formatng

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.

Collapse -

by Rafiki In reply to Vlookup ~ formatng

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.

Collapse -

by Choppit In reply to Vlookup ~ formatng

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.

Related Discussions

Related Forums