VLOOKUP Problem

  • Thread starter Thread starter Jim Rowley
  • Start date Start date
J

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
 
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)
 
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)
 
Back
Top