How do I get rid of N/A in cells while using VLOOKUP in Excel
 

I have set up a page that will extract information from another workbook
using vlookup, the information also has a cost assigned to it but I can't get
a running total of the items because the cells I'm not using have #N/A in
them. Is there any way to clear the cells of #N/A but not erase the formula
so that as I enter information I can see what the running cost is at the
bottom?

How do I get rid of N/A in cells while using VLOOKUP in Excel
 

Kyle,

IF(ISNA(VLOOKUP(lookup_value,table_array,col_index _num)),0,VLOOKUP(lookup_value,table_array,col_inde x_num))

This is to eliminate the #N/A error only and replace it with 0. For
other types of errors, replace the ISNA with ISERROR.

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=506418

How do I get rid of N/A in cells while using VLOOKUP in Excel
 

=IF(ISNA(lookup_formula),"",lookup_formula)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"kYle" wrote in message
...
I have set up a page that will extract information from another workbook
using vlookup, the information also has a cost assigned to it but I can't
get
a running total of the items because the cells I'm not using have #N/A in
them. Is there any way to clear the cells of #N/A but not erase the
formula
so that as I enter information I can see what the running cost is at the
bottom?

How do I get rid of N/A in cells while using VLOOKUP in Excel
 

You can change your formula:

=if(iserror(vlookup(...)),"Missing or 0??",vlookup(...))



kYle wrote:

I have set up a page that will extract information from another workbook
using vlookup, the information also has a cost assigned to it but I can't get
a running total of the items because the cells I'm not using have #N/A in
them. Is there any way to clear the cells of #N/A but not erase the formula
so that as I enter information I can see what the running cost is at the
bottom?

--

Dave Peterson

How do I get rid of N/A in cells while using VLOOKUP in Excel
 

Other ways:

=SUMIF(A1:A10,"<#N/A")

=SUM(IF(ISNUMBER(A1:A10),A1:A10))
=SUM(IF(NOT(ISNA(A1:A10)),A1:A10))
these last 2 are array formulae...enter using CTRL+SHIFT+ENTER

HTH
JG

"Bob Phillips" wrote:

=IF(ISNA(lookup_formula),"",lookup_formula)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"kYle" wrote in message
...
I have set up a page that will extract information from another workbook
using vlookup, the information also has a cost assigned to it but I can't
get
a running total of the items because the cells I'm not using have #N/A in
them. Is there any way to clear the cells of #N/A but not erase the
formula
so that as I enter information I can see what the running cost is at the
bottom?