Vlookup Without #N/A Error

**prr** · 12-01-2009, 03:51 PM

I'm trying to do a vlookup where I can sum the vlookup results from several tables where the name I'm looking for sometimes does not appear on a few of the tables. When the name does not appear in a table it results in an #N/A, then where I have included that in my sum formula my answer is also NA. Is there a way for the summed vlookups to result in a value even if some of the results are NA?

Thank you!!!

**NBVC** · 12-01-2009, 03:56 PM

You can add error handler to the Vlookup formulas

eg. =IF(ISNA(Vlookup(....)),"",Vlookup(....))

which will return a blank instead of error...

**prr** · 12-01-2009, 04:15 PM

i'm not getting the results i need, of adding up the associated #s in the tables, i'm playing around but don't see what i'm doing wrong. could you take a look at cell b31 (attached)?

Thanks

**NBVC** · 12-01-2009, 04:19 PM

You didn't put in the 2nd Vlookup() to tell the formula that it needs to do the vlookup if the Isna() is false

Also, use 0 instead of "" to avoid the #Value error if item not found

Try:

=IF(ISNA(VLOOKUP($A31,$A$5:$B$27,2,FALSE)),0,VLOOKUP($A31,$A$5:$B$27,2,FALSE))+IF(ISNA(VLOOKUP($A31,$C$5:$D$27,2,FALSE))*0,VLOOKUP($A31,$C$5:$D$27,2,FALSE))