Avoiding INDIRECT()

Question

I have data in row #3 like:

and I want to locate the first two occurrences of the word Pass.

The first occurrence is easy; in C6 I enter:

=MATCH("Pass",3:3,0)

and it correctly yields 3 (Column C). To get the position of the second occurrence, I want to use MATCH() starting one column to the right of the first occurrence, that is D3:IV3, but based on the result I already have in C6 I know I can get the D with:

=SUBSTITUTE((LEFT(ADDRESS(1,C6+1),3)),"$","")

So I am using:

=MATCH("Pass",INDIRECT(SUBSTITUTE((LEFT(ADDRESS(1,C6+1),3)),"$","") & "3:IV3"),0)+C6

This works:

But I can't believe that this extremely ugly formula is the best way to find the second occurrence. Any suggestions ??

Answer 1

Use AGGREGATE as a SMALL(IF()) to get the second column with Pass:

=AGGREGATE(15,6,COLUMN(A:Z)/(A3:Z3="Pass"),2)

Where 2 is the occurrence.

---

I know how much you dislike array formulas so since you already know the first you can find the second with this formula:

=MATCH("Pass",INDEX(3:3,D5+1):INDEX(3:3,15000),0)+D5