You can use the following formula to return the second instance of a matching value in Excel:

=INDEX(B1:B11,SMALL(IF(A1:A11=F1,ROW(A1:A11)-MIN(ROW(A1:A11))+1),2))

This particular formula will look up the value in cell **F1** in the range **A1:A11** and then return the value that corresponds to the second instance of the matching value from the range **B1:B11**.

**Note**: To find the n^{th} instance of a matching value, simply change the last **2** in the formula to *n*.

The following example shows how to use this formula in practice.

**Example: Find Second Instance of Matching Value in Excel**

Suppose we have the following dataset in Excel that contains information about various basketball players:

Suppose we would like to return the value in the points column that corresponds to the second instance of “Mavs” in the team column.

We can type the following formula into cell **F2** to do so:

=INDEX(B1:B11,SMALL(IF(A1:A11=F1,ROW(A1:A11)-MIN(ROW(A1:A11))+1),2))

The following screenshot shows how to use this formula in practice:

The formula returns a value of **24**, which is the value in the points column that corresponds to the second instance of “Mavs” in the team column.

If we would like to return the value in the assists column instead, we could change the cell range **B1:B11** to **C1:C11**:

=INDEX(C1:C11,SMALL(IF(A1:A11=F1,ROW(A1:A11)-MIN(ROW(A1:A11))+1),2))

The following screenshot shows how to use this formula in practice:

The formula returns a value of **7**, which is the value in the assists column that corresponds to the second instance of “Mavs” in the team column.

**Note**: If a second instance does not exist for the value that you look up, then the formula will return **#NUM!** as a result.

**Additional Resources**

The following tutorials explain how to perform other common tasks in Excel:

Excel: Count Number of Matches Between Two Columns

Excel: How to Find All Values that Match Criteria

Excel: How to Find Matching Values in Two Worksheets