Help showing #N/A as a 0

[HughBuchan]

Looking for a little help...

I am using a vlookup to pull data into a spreadsheet that summarizes information about marketing campaigns. Currently if the vlookup does not find data on a specific lookup it returns a #N/A.

I have a row that totals up all the results of each campaign, but if one of the results that is incorportated in the total is a #N/A then the total shows up as a #N/A. To avoid this I have been going in and typing in a 0, but I'm trying to make these spreadsheets to be as automated as possible, and have lookup return a zero if it does not find data.

Is there a way to do this?

Thanks,

-hugh

 

[NBVC]

Incorporate your vlookup in an IF() statement

e.g =If(ISNA(vlookup()),0,vlookup())

replacing the Vlookup() with your actual Vlookup formula.

 

[Aladin Akyurek]

Looking for a little help...

I am using a vlookup to pull data into a spreadsheet that summarizes information about marketing campaigns. Currently if the vlookup does not find data on a specific lookup it returns a #N/A.

I have a row that totals up all the results of each campaign, but if one of the results that is incorportated in the total is a #N/A then the total shows up as a #N/A. To avoid this I have been going in and typing in a 0, but I'm trying to make these spreadsheets to be as automated as possible, and have lookup return a zero if it does not find data.

Is there a way to do this?

Thanks,

-hugh

You can keep #N/A's as is and invoke:

=SUM(SUMIF(Range,{"<0",">0"}))

to sum the range that includes #N/A's.

 

[ElBombay]

An expansion on the "ISNA" response would be

If(ISERROR(vlookup()),0,vlookup())

where the "vlookup()" can be repalced with an equation or any other function and the 0 can be replaced with a flag or instruction, as the situation requires.