VLOOKUP Problem

[Jim Rowley]

Hi,
When I use VLOOKUP to find information in a table, if the
value I am searching for is not in the table, it displays
the nearest match.
I do not want this but would like to show no value or some
text to say not in table.
Is there any way of achieving this please?
Kind regards,
JJ Rowley

 

[Max]

Put in FALSE (or "0")
as the 4th parameter (i.e. the range_lookup)
in the VLOOKUP, for an exact match

For example:

=VLOOKUP(A1,MyTable,2,FALSE)

or

=VLOOKUP(A1,MyTable,2,0)

 

[Max]

Apologies, earlier suggestion was incomplete.

To return a zero, "blank", "Not in Table" etc
for un-matched cases using FALSE as the range_lookup,
try using a construct like:

=IF(ISNA(VLOOKUP(...),0,VLOOKUP(...))
(returns zero if no match)

=IF(ISNA(VLOOKUP(...),"",VLOOKUP(...))
(returns "blank" if no match)

=IF(ISNA(VLOOKUP(...),"Not in Table",VLOOKUP(...))
(returns "Not in Table" if no match)

So the complete expression would look something like:
=IF(ISNA(VLOOKUP(A1,MyTable,2,FALSE),"",VLOOKUP(A1,MyTable,2,FALSE))
(returns "blank" if no match)