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
Hi Zach, I was wondering if you knew the formula for comparing 3 columns in excel. For example, I have 3 columns of names. I am trying to find if the names in Column C appear in either Column B or Column G.
Thank you!
Hi Dee…You can use a combination of `INDEX`, `MATCH`, and `OR` functions to achieve this in Excel. However, for checking whether a name in Column C appears in either Column B or Column G, a simpler approach would be to use the `IF`, `OR`, and `COUNTIF` functions to compare values across the columns.
Here’s the formula that checks whether the names in Column C appear in either Column B or Column G:
### Formula:
“`excel
=IF(OR(COUNTIF(B:B,C1), COUNTIF(G:G,C1)), “Match”, “No Match”)
“`
### Explanation:
– `COUNTIF(B:B, C1)`: This checks if the value in cell `C1` appears anywhere in Column B.
– `COUNTIF(G:G, C1)`: This checks if the value in cell `C1` appears anywhere in Column G.
– `OR`: The `OR` function returns TRUE if either of the `COUNTIF` results is greater than 0 (i.e., the name exists in either Column B or Column G).
– `IF`: The `IF` function returns “Match” if the `OR` condition is TRUE, otherwise it returns “No Match”.
### Steps:
1. Copy the formula to cell D1 (or the next available column).
2. Drag it down to apply the formula for all rows in Column C.
This formula will compare each name in Column C with the names in Column B and Column G and will return “Match” if a name exists in either of the other two columns, and “No Match” if it doesn’t.