INDEX-AGGREGATE with OR criteria

[Glenn Kennedy]

Hi there folks. I'm on the other side of the Question-Answer axis for a change. This arose as a result of some random musings following my contribution to another thread here.

Aim: INDEX-AGGREGATE to return multiple results, in a non-array formula, that works when the or criteria are either in columns or in rows.

I know that that or criteria can be accommodated using ((criterion range =criterion 1)+(criterion range = criterion range 2)+....)

Thats fine for 2 or three. But what if you wanted to have a variable number UP TO, say 10. Then that approach would be messy.

If the OR criteria are in a row and are numbers (Cols J-L), and all 3 places are filled, no problem (column D). However, delete one of the 3 OR criteria and it falls over. E.g. delete 6 from L3. There is a "fix" in column E. It's immmune to the effect of deleting L3. It does not wotk if the OR criteria are text (column Q).


Question 1. What "fix" to I need to apply to column R to provide the text equivalent of column E, while KEEPING the formula non-array?

If the OR criteria are numbers and in a column, I need to use TRANSPOSE (column G). The "fix" still works (column H), but both column G & H are array formulae. That's a pity, since it's normally not needed with AGGREGATE.

Question 2. Is there a non-array alternative to TRANSPOSE when the OR criteria are in COLUMNS?

Finally,

Question 3. Do the fixes still work in any non-array formulae when the OR criteria are in columns? If not, how can they be adapted to work?

I'm sure that it's do-able... but I can't "see the wood for the trees" any more. Have fun...

[Phuocam]

Use COUNTIF()

in D3:

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($A$3:$A$14)/COUNTIF($J$3:$L$3,$A$3:$A$14),ROWS($1:1))),"")

in G3:

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($A$3:$A$14)/COUNTIF($J$6:$J$8,$A$3:$A$14),ROWS($1:1))),"")

[Glenn Kennedy]

Cheers!! Brilliant job.

You made it look so easy. You wouldn't believe the brain-pain I was getting!!