CODEWITHSUNDEEP
arraysexcelif-statement
T.J. Brooks
T.J. Brooks

Reputation: 11

Excel Return Array if condition is true

I am trying to get something of the effect that when I have an if statement: if({TRUE;FALSE;TRUE},{1,2,3}) it will return with {1,3} is there any way to get this effect with an array of conditions and return an array of results?

Upvotes: 1

Views: 1596

Answers (2)

Scott Craner
Scott Craner

Reputation: 152660

If one has the Dynamic Array Formula FILTER:

=FILTER({1,2,3},TRANSPOSE({TRUE;FALSE;TRUE}))

Without dynamic Array formula:

=TRANSPOSE(MODE.MULT(IF({TRUE;FALSE;TRUE},ROW(1:3)*{1,1})))

This is an array formula and depending on ones version, it may need to be confirmed with Ctrl-Shift-Enter when exiting edit mode.

Upvotes: 1

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60464

This formula will do what you say you want.

Note that there are a bunch of TRANSPOSE functions. This is because your Booleans are semicolon separated and your condition_if_true result is comma separated.

 =TRANSPOSE(AGGREGATE(15,6,IF(1/{TRUE;FALSE;TRUE},TRANSPOSE({1,2,3})),ROW(INDIRECT("1:"& SUM(--{TRUE,FALSE,TRUE})))))

enter image description here

Upvotes: 0

Related Questions