Replacing #N/A response when obtained in Lookup function
 

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?

One way:

=IF(ISNA(MATCH(A1,J:J,FALSE)),"Not found",VLOOKUP(A1,J:K,2,FALSE))

In article ,
"CochranConsult" wrote:

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?

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

"CochranConsult" wrote:

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?

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

On Sat, 14 May 2005 09:32:03 -0700, "CochranConsult"
wrote:

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?

Replacing #N/A response when obtained in Lookup function
 

This was a great help to me!! Thanks.

"Gord Dibben" wrote:

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

On Sat, 14 May 2005 09:32:03 -0700, "CochranConsult"
wrote:

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?