Table of Contents
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:
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.