# INDEX-AGGREGATE with OR criteria

1. ## INDEX-AGGREGATE with OR criteria

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

2. ## Re: INDEX-AGGREGATE with OR criteria

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))),"")

3. ## Re: INDEX-AGGREGATE with OR criteria

Cheers!! Brilliant job.

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1