Vlookup & Iserror to cope with blanks

[Paul Olson]

A problem using vlookup is that blanks in the lookup table
give a result of N/A, so you cannot sum the results. I can
use a seperate formula in a seperate column for Iserror to
remove the N/A and replace it with 0. Can anyone suggest
the syntax which combines Vlookup and iserror into one
formuala, so that when there are blanks in the data I can
sum the results.

Example:
C25 has vlookup formuala
D25 =if(iserror(c25)=true,0,c25)

Thanks for looking,
Paul

 

[Ron Rosenfeld]

A problem using vlookup is that blanks in the lookup table
give a result of N/A, so you cannot sum the results. I can
use a seperate formula in a seperate column for Iserror to
remove the N/A and replace it with 0. Can anyone suggest
the syntax which combines Vlookup and iserror into one
formuala, so that when there are blanks in the data I can
sum the results.

Example:
C25 has vlookup formuala
D25 =if(iserror(c25)=true,0,c25)

Thanks for looking,
Paul

If lookup_value is in A1, and the first column of table_array is called
FirstCol, then:

=IF(COUNTIF(FirstCol,lookup_value)=0,0,vlookup formula)


--ron