You can use the INDEX and MATCH functions in Excel to look up a specific value in one column and return a corresponding value in another column.
You can use the following syntax with INDEX and MATCH to look up a value in one column where specific criteria are met in 3 other columns:
=INDEX(D2:D10,MATCH(1,(G1=A2:A10)*(G2=B2:B10)*(G3=C2:C10),0))
This particular formula looks up the value in the range D2:D10 where the following three criteria are met:
- A2:A10 is equal to the value in cell G1
- B2:B10 is equal to the value in cell G2
- C2:C10 is equal to the value in cell G3
The following example shows how to use this syntax in practice.
Example: How to Use INDEX MATCH with 3 Criteria in Excel
Suppose we have the following dataset in Excel that contains information about various basketball players:
Suppose we would like to look up the points value of the player that meets the following three criteria:
- Team = Mavs
- Position = Guard
- All-Star = No
We can type the following formula into cell G4 to do so:
=INDEX(D2:D10,MATCH(1,(G1=A2:A10)*(G2=B2:B10)*(G3=C2:C10),0))
The following screenshot shows how to use this formula in practice:
We can see that the value in the points column that corresponds to Mavs in the team column, Forward in the Position column and No in the All-Star column is 18.
We can manually verify this is correct by identifying the player that meets these three criteria:
We can see that the player on the Spurs team with a position of Forward and an all-star status of No does indeed have 18 points.
Feel free to change the criteria in cells G1, G2 and G3 to look up the points value of a player who meets three different criteria.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: How to Use IF Function with INDEX MATCH
Excel: How to Use SUM with INDEX MATCH
Excel: Use INDEX and MATCH to Return Multiple Values Vertically
