Excel: Find First Occurrence Based on Multiple Criteria

You can use the following formula to find the first occurrence of a value in a column in Excel based on multiple criteria:

=INDEX(C2:C13,MATCH(1,INDEX((A2:A13=F1)*(B2:B13=F2),),FALSE))

This particular formula returns the first value in the range C2:C13 where the corresponding value in A2:A13 is equal to the value in cell F1 and the corresponding value in B2:B13 is equal to the value in cell F2.

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

Example: Find First Occurrence Based on Multiple Criteria in Excel

Suppose we have the following dataset that contains information about points scored by various basketball players:

Suppose we would like to return the points value for the first occurrence of a player who is on the Spurs team and has a position of Forward.

We can specify this criteria in cells F1 and F2, then type the following formula into cell F3:

=INDEX(C2:C13,MATCH(1,INDEX((A2:A13=F1)*(B2:B13=F2),),FALSE))

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

Excel find first occurrence based on multiple criteria

The formula returns a points value of 31, since this represents the points value for the first player to be on the Spurs team and have a position of Forward.

Note that if we change the criteria in cells F1 and F2, the formula will automatically return a new player who matches the new criteria.

For example, suppose we change the team to Rockets and the position to Guard:

The formula correctly returns a value of 12, which is the points value that corresponds to the first player to be on the Rockets team and have a position of Guard.