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

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
 
A

Alan

(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,
 
A

Arun Jhalani

You can Condtional Format the Sheet for as: ISerror(),then
Cell format to be Blank!!
 
K

Kem

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can I add a formula or function to Vlookup? 1
Eliminate #N/A in Vlookup formulas 2
trouble with (complicated) vlookup searching another workbook 2
Multiple IF Statement 2
Vlookups to return a N/A value 3
Vlook up value is #N/A 4
VLOOKUP AND #N/A 10
Vlookup and return sheet name also 2

Top