non numeric data summed as "0"

E

ejohnson

I use VLOOKUP a lot on long lists. When there is no match
for an item, it returns #NA error message. Need to have
the SUM function treat those "NA"s as 0, or get VLOOKUP
to return 0 when there is no match for an item. Ideas?
 
J

J.E. McGimpsey

One way:

If your VLOOKUP is

=VLOOKUP(A1,J1:K100,2,FALSE)

instead use:

=IF(ISNA(MATCH(A1,J1:J100,FALSE)),0,VLOOKUP(A1,J1:K100,2,FALSE)
 
P

Peo Sjoblom

A couple of ways, instead of sum use

=SUMIF(A2:A50,"<>#N/A")

that way you would keep the errors since they can be good for auditing
formulas,
else use

=IF(ISNA(MATCH(A1,Sheet2!A1:A20,0)),0,VLOOKUP(A1,Sheet2!A1:C20,2,0))
 
B

Bob Phillips

Something like

=IF(ISNA(VLOOKUP(lookup_value,lokkup_table,offset,FALSE)),0,VLOOKUP(lookup_v
alue,lokkup_table,offset,FALSE))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
B

Bigdotnetpapa

=IF(ISNA(VLOOKUP(lookup_value,table_range,column_index,FALSE)),0,VLOOKUP(loo
kup_value,table_range,column_index,FALSE))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top