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...
Bookmarks