Doubt with Array formula - Can it be preceeded by an "IF"?

hi there!

I have this doubt.

Look at this formula:

=IF(C1=0,””,C1)

This formula commands Excel to return nothing if cell C1 is empty; otherwise the result should be the content of cell C1.

Can I use a similar formula to command Excel to return nothing if an array formula returns "0" as the result? I mean something like this:

=IF(Array formula =0,"",Array formula)

The array formula I am using is this one:

Formula:

Please Login or Register  to view this content.

If this is possible, I don't know what the syntax would be to write the formula, or when I should click CTL+SHIFT+ENTER to activate the array formula if it is preceeded by an "IF".

Originally Posted by alchavar

=IF(C1=0,””,C1)

This formula commands Excel to return nothing if cell C1 is empty

That's not exactly true.
It returns nothing if C1 equals Zero.
It just so happens that it will consider an empty cell as 0, so it works as you intend.
But strictly speaking it's also going to return the "" if C1 contains the numerical value of Zero.

Anyway..

Yes, you can wrap your existing Array formula in an IF to test it's result for 0.
FYI, your array formula is already in an IF..Testing if it's result is an error (IF(ISERROR(...)
The array portion of your formula is just this part
INDEX($A$1:$B$10000,SMALL(IF($A$1:$A$10000=E$1,ROW($A$1:$A$10000)),ROW(1:1)),2)

To test it for 0, try
=IF(ISERROR(INDEX($A$1:$B$10000,SMALL(IF($A$1:$A$10000=E$1,ROW($A$1:$A$10000)),ROW(1:1)),2)),"",IF(INDEX($A$1:$B$10000,SMALL(IF($A$1:$A$10000=E$1,ROW($A$1:$A$10000)),ROW(1:1)),2)=0,"",INDEX($A$1:$B$10000,SMALL(IF($A$1:$A$10000=E$1,ROW($A$1:$A$10000)),ROW(1:1)),2)))

Thanks all of you who responded!

Though the answer from "mcmahobt" makes sense, I tried using the answer of "Jonmo1" shown above in order to wrap my array formula in an IF to test its result for 0, and it worked.

You're welcome.