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

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


--
JOConno
-----------------------------------------------------------------------
JOConnor's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1661
View this thread: http://www.excelforum.com/showthread.php?threadid=31482

0
11/18/2004 11:54:20 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
467 Views

Similar Articles

[PageSpeed] 21

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

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

"JOConnor" wrote:

> 
> Hi 
> I have a large spreadsheet of Data that contains a Vlook up to another
> sheet. I need to sum the figures however the V-Look up is returning
> some N/A values which the autosum doen't recognize. How do I delete
> these or make them equal to zero?
> 
> thanks
> j
> 
> 
> -- 
> JOConnor
> ------------------------------------------------------------------------
> JOConnor's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16618
> View this thread: http://www.excelforum.com/showthread.php?threadid=314822
> 
> 
0
frank.kabel (11126)
11/18/2004 12:09:07 PM
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


0
11/18/2004 12:24:12 PM
Reply:

Similar Artilces: