VLookup values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All

Can anyone please tell me how to write a Vlookup query and not get the #N/A if any of the references are not present in the range?i.e for it to return a blank or zero
 
Hi Yinka!

The standard approach is:
=IF(ISNA(YourFormula),"",YourFormula)

You could use ISERROR but its preferable to trap the specific error
using ISNA.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Yinka said:
Hi All,

Can anyone please tell me how to write a Vlookup query and not get
Click to expand...
the #N/A if any of the references are not present in the range?i.e for
it to return a blank or zero
 
Try this

=IF(ISNA(vlookup($A$1:$D$200,A1,2,FALSE)),"",vlookup($A$1:$D$200,A1,2,FALSE))

Or replace the "" with 0

Hope this helps

Judith
 
I think Judith meant this:

=IF(ISNA(vlookup(a1,$A$1:$D$200,2,FALSE)),"",vlookup(a1,$A$1:$D$200,2,FALSE))

Although usually the cell to use to look up isn't in the range:

=IF(ISNA(vlookup(a1,sheet2!$A$1:$D$200,2,FALSE)),"",
vlookup(a1,sheet2!$A$1:$D$200,2,FALSE))

(maybe more typical)
 
Back
Top