Table of Contents
INDEX MATCH MATCH is a powerful function in Microsoft Excel that allows users to lookup and retrieve data from a specific cell in a table based on multiple criteria. This function uses the INDEX function to return the value of a cell in a given table, and the MATCH function to specify the row and column where the desired value is located. By using this combination, users can perform a more advanced and precise lookup, making it a useful tool for data analysis and manipulation in Excel. This function is particularly helpful when working with large datasets or when the data is not organized in a traditional table format. Overall, INDEX MATCH MATCH is a versatile and efficient way to retrieve data in Excel.
Use INDEX MATCH MATCH in Excel (With Example)
You can use INDEX MATCH in Excel to look up a vertical value in a column and return a corresponding value in another column.
You can take this one step further by using INDEX MATCH MATCH to look up both a horizontal value and vertical value and return the corresponding cell in a matrix.
You can use the following basic syntax to do so:
=INDEX(A1:E7, MATCH(B9, A1:A7,0), MATCH(B10, A1:E1,0))
This particular formula looks up the value in cell B9 in the vertical range A1:A7, then looks up the value in cell B10 in the horizontal range A1:E1 and returns the corresponding cell value where these two lookup values intersect in the overall range of A1:E7.
The following example shows how to use this syntax in practice.
Example: How to Use INDEX MATCH MATCH in Excel
Suppose we have the following dataset in Excel that shows the sales made at various retail stores for some company during each quarter:
Suppose we would like to look up the sales value for the West region during Quarter 3.
We can type the following formula into cell B11 to extract this value:
=INDEX(A1:E7, MATCH(B9, A1:A7,0), MATCH(B10, A1:E1,0))
The following screenshot shows how to use this formula in practice:
The formula returns a value of 37, which corresponds to the sales value for the West region during Quarter 3.
If we change the values for the region and quarter, the formula will update the sales value accordingly.
For example, suppose we change the region to Pacific and the quarter to Quarter 4:
The formula returns a value of 34, which corresponds to the sales value for the Pacific region during Quarter 4.
The following tutorials explain how to perform other common operations in Excel:
Cite this article
stats writer (2024). How can I use INDEX MATCH MATCH in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-index-match-match-in-excel/
stats writer. "How can I use INDEX MATCH MATCH in Excel?." PSYCHOLOGICAL SCALES, 22 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-index-match-match-in-excel/.
stats writer. "How can I use INDEX MATCH MATCH in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-index-match-match-in-excel/.
stats writer (2024) 'How can I use INDEX MATCH MATCH in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-index-match-match-in-excel/.
[1] stats writer, "How can I use INDEX MATCH MATCH in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use INDEX MATCH MATCH in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.