Table of Contents
INDEX MATCH is a powerful function in Excel that allows you to retrieve data based on specific criteria. It combines the use of the INDEX function to find the row and column of the data you want to retrieve, and the MATCH function to specify the criteria for the search. By using 3 criteria, you can refine your search and retrieve more accurate and specific data. This can be particularly useful when dealing with large data sets. Simply input the three criteria into the INDEX MATCH formula and it will return the corresponding data that matches all three criteria. This makes it an efficient and effective method for retrieving data in Excel.
Excel: Use INDEX MATCH with 3 Criteria
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.
The following tutorials explain how to perform other common operations in Excel:
Cite this article
stats writer (2024). How can I use INDEX MATCH with 3 criteria to retrieve data in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-index-match-with-3-criteria-to-retrieve-data-in-excel/
stats writer. "How can I use INDEX MATCH with 3 criteria to retrieve data in Excel?." PSYCHOLOGICAL SCALES, 22 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-index-match-with-3-criteria-to-retrieve-data-in-excel/.
stats writer. "How can I use INDEX MATCH with 3 criteria to retrieve data in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-index-match-with-3-criteria-to-retrieve-data-in-excel/.
stats writer (2024) 'How can I use INDEX MATCH with 3 criteria to retrieve data in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-index-match-with-3-criteria-to-retrieve-data-in-excel/.
[1] stats writer, "How can I use INDEX MATCH with 3 criteria to retrieve data in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use INDEX MATCH with 3 criteria to retrieve data in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.