How can I get the location of a cell in Excel based on its value if it's not the first one in the column?

Question

I know how to use index and match formulas to get the value or location of a matching cell. But what I don't know how to do is get that information when the cell I'm looking for isn't going to be the first match.

Take the image below for example. I want to get the location of the cell that says "Successful Deliveries". In this example there's a cell that matches that in rows 11 and 30. These locations can vary in the future so I need a formula that's smart enough to handle that.

How would I get the location of the second instance of "Successful Deliveries"? I figured I could use the "Combination 2 Stats" value from row 24 as a starting point.

I tried using this formula:

=MATCH("Successful Deliveries:",A24:A1000,0)

But it returns a row number of 7 which is just relative to the A24 cell I started my match at.

My end goal here is to get the value from the cell directly to the right of the second match of "Successful Deliveries".

Answer 1

The full answer is here:

https://exceljet.net/formula/get-nth-match-with-index-match

You use this formula:

=INDEX(B1:B100,SMALL(if(A1:A100 = "Successful Deliveries:",ROW(A1:A100) - ROW(INDEX(A1:A100,1,1))+1),2))

...where 2 is the instance you want.

Make sure to finish typing the formula by hitting ctrl-shift-enter. (You know you did this right because the formula gets curly brackets {})

HOW IT WORKS

Normally, we use INDEX / MATCH to find a value. The Index function gives you the nth value in a range, and the Match function determines which "n" is a match for our criteria.

Here we use INDEX the same way, but we need more intelligence to find that "n", since it's the second one that matches the criteria. That's where SMALL comes in. The Small function "gets the nth smallest value in an array". So we give Small the number of the desired instance (2 in this case) and we give it an array of blanks and the rows numbers of the rows we like.

We obtained the array of blanks and row numbers using the If function, asking it to check for our criterion (="Successful...") and making it return the row number where the criterion passes (=Row(A1:A100)). By using the If function as an array function (by giving it arrays and using ctrl-shift-enter) it can deliver a whole list of values.

Our final value is just one number because the Small function used the array from the IF to return just one thing: the second-smallest row we gave to it.

Answer 2

In your formula, with no further intelligence, you can simply add 23 to adjust 7 to the result:

=MATCH("Successful Deliveries:",A24:A1000,0) + 23

You know that 23 is the number to add because you started your search on row 24.