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

K

kYle

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?
 
S

SteveG

Kyle,

IF(ISNA(VLOOKUP(lookup_value,table_array,col_index_num)),0,VLOOKUP(lookup_value,table_array,col_index_num))

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

HTH

Stev
 
B

Bob Phillips

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
D

Dave Peterson

You can change your formula:

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

pinmaster

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
 
Top