IF statement with returning value -> if value blank, place a zero

ZennerJ

Hi, I am trying to fill in a row with numbers from a pivot sheet I created. If the pivot value is blank, I need a zero to be placed in the field the blank value is returning to:

 

IF(NOT(ISBLANK(INDEX({Pivot sheet Active value range}, MATCH([Identity ID]@row, {Pivot sheet Identity ID range}, 0)))), "0").

 

This formula is bringing back a blank value when it has a value in the pivot sheet,

and zero for all others. I need to have the "Active value" on the pivot sheet to

show, and place a zero if nothing is there.

I've tried it as IF(ISBLANK) and IF(NOT(ISBLANK) and can't get it to work. I don't see any settings on the pivot workflow that forces zeros into all blank fields, so the formula seems to be the only option.

Does anyone see the error in my formula or know of a way to have zeros appear on the pivot sheet when no value is reported?

 

Paul Newcome

The way your formula is currently written, you are essentially saying "If the output of the INDEX/MATCH is not blank then output a zero."

What you want to say is "it the output of the INDEX/MATCH is not blank, then output the INDEX/MATCH, otherwise output zero."

=IF(NOT(ISBLANK(INDEX({Pivot sheet Active value range}, MATCH([Identity ID]@row, {Pivot sheet Identity ID range}, 0)))), INDEX({Pivot sheet Active value range}, MATCH([Identity ID]@row, {Pivot sheet Identity ID range}, 0)), "0")

Paul Newcome

The way your formula is currently written, you are essentially saying "If the output of the INDEX/MATCH is not blank then output a zero."

What you want to say is "it the output of the INDEX/MATCH is not blank, then output the INDEX/MATCH, otherwise output zero."

=IF(NOT(ISBLANK(INDEX({Pivot sheet Active value range}, MATCH([Identity ID]@row, {Pivot sheet Identity ID range}, 0)))), INDEX({Pivot sheet Active value range}, MATCH([Identity ID]@row, {Pivot sheet Identity ID range}, 0)), "0")

ZennerJ

Thank you! I couldn't figure out why that was happening - it worked!

Paul Newcome

Happy to help. 👍️