Replacing #N/A response when obtained in Lookup function

G

Guest

When using Vlookup and there is no direct match the response is "#N/A",
telling me that there is no value in the lookup table. The problem is that I
have to sum the results of the lookup function. Since some of the values are
"#N/A" the sum of the column is "#N/A". How can I easily replace the "#N/A"
with either text or a "0" so that the sum formula for the column produces an
answer?
 
G

Guest

One method:

=IF(ISNA(VLOOKP(---)),"",VLOOKUP(---))

or:

=IF(COUNTIF(lookup_column,lookup_value),VLOOKUP(---),"")

Or you could leave your VLOOKUP as is and use:

=SUMIF(A:A,"<>#N/A")

HTH
Jason
Atlanta, GA
 
G

Gord Dibben

Use the ISNA error trap.

=IF(ISNA(VLOOKUP(E2,F2:H19,2,FALSE)),"",VLOOKUP(E2,F2:H19,2,FALSE))

Change the "" to 0 if that is what you want displayed.


Gord Dibben Excel MVP
 
G

Guest

This was a great help to me!! Thanks.

Gord Dibben said:
Use the ISNA error trap.

=IF(ISNA(VLOOKUP(E2,F2:H19,2,FALSE)),"",VLOOKUP(E2,F2:H19,2,FALSE))

Change the "" to 0 if that is what you want displayed.


Gord Dibben Excel MVP
Click to expand...
 

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

Set add function ignore #N/A 1
#N/A in lookup function 3
Scrabble Value calculation for Welsh words 0
Sum Result of lookup in excel 2007 4
LOOKUP function yielding a #N/A result 6
Sum Function When Formula Produces #N/A 2
Vlookups to return a N/A value 3
Sum and "N/A" 3

Top