Vlookup & Iserror to cope with blanks

  • Thread starter Thread starter Paul Olson
  • Start date Start date
P

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
 
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
Click to expand...

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
 
Back
Top