Deleting N/A value in a spreadsheet with V-Look Up

J

JOConnor

Hi
I have a large spreadsheet of Data that contains a Vlook up to anothe
sheet. I need to sum the figures however the V-Look up is returnin
some N/A values which the autosum doen't recognize. How do I delet
these or make them equal to zero?

thanks
 
F

Frank Kabel

Hi
you may change your formulas to
=IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...))

or for summing use
=SUMIF(A1:A100,"<>#NA")
 
C

CycleZen

One way to do this is to replace each VLOOKUP with a nested IF(), ISERROR(),
and VLOOKUP() function. In the mockup I created I used this formula:
=VLOOKUP(A2,$A$1:$B$4,2)
which returned #N/A.

I replaced it with
=IF(ISERROR(VLOOKUP(A2,$A$1:$B$4,2),0,VLOOKUP(A2,$A$1:$B$4,2))

This formula evaluates the VLOOKUP and returns 0 if the VLOOKUP is #N/A. If
the VLOOKUP does not return an error it performs the VLOOKUP.

Dave O
 
Top