non numeric data summed as "0"

  • Thread starter Thread starter ejohnson
  • Start date Start date
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?
 
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)
 
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))
 
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)
 
=IF(ISNA(VLOOKUP(lookup_value,table_range,column_index,FALSE)),0,VLOOKUP(loo
kup_value,table_range,column_index,FALSE))
 
Back
Top