0

I have data in row #3 like:

enter image description here

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:

enter image description here

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

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • 1
    See [How to return the second non-blank cell from row](http://stackoverflow.com/questions/39374379/how-to-return-the-second-non-blank-cell-from-row-excel-for-mac/39374473#39374473) from earlier today. –  Sep 07 '16 at 20:38

1 Answers1

4

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.

enter image description here

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

![enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • 1
    @Gary'sStudent I edited the post (again) to use your helper cell with the first instance, thus avoiding the array formula. – Scott Craner Sep 07 '16 at 20:50