How can I keep "#N/A" values from appearing in VLOOKUP results?

  • Thread starter Thread starter shadestreet
  • Start date Start date
S

shadestreet

When I run a VLOOKUP on an array, and the value I am searching for i
not found, I get the error "#n/a" which is quite annoying. How can
run a VLOOKUP that leaves the cell blank (or at least a zero) if ther
is not a value to match against
 
(example formula)
=IF(ISNA(VLOOKUP(D2,H3:I5,2,FALSE)),0,VLOOKUP(D2,H3:I5,2,FALSE))
or
=IF(ISERROR(VLOOKUP(D2,H3:I5,2,FALSE)),0,VLOOKUP(D2,H3:I5,2,FALSE))
You can change the result to your requirements eg
=IF(ISERROR(VLOOKUP(D2,H3:I5,2,FALSE)),"Invalid
Entry",VLOOKUP(D2,H3:I5,2,FALSE))
etc,
Regards,
 
You can Condtional Format the Sheet for as: ISerror(),then
Cell format to be Blank!!
 
Hi,

Try the following:

=if(or(a1=FirstColumn),
vlookup(a1,SourceTable,ColNo,false),"")

where
a1 = the value you're looking up
FirstColumn = the first column of SourceTable
SourceTable = the table you're looking for the result
ColNo = the column that you want to pull from

Since the or() function is an array formula, you have to
do Ctrl-Shift-Enter to enter.

Regards,
Kem
 
Back
Top