Download the free workbook 👉 HERE and follow along.
When working with reports in Excel, you might need to find values based on multiple criteria in both rows and columns. For example, imagine you have a table with sales data. Your task is to lookup the sales amount for a specific product in a specific region during a specific month.
The main challenge here is that the Excel MATCH function can only handle a single row or column, not a combination. Here’s how you can work around this limitation to find values based on multiple criteria.
Find the introduction to Excel INDEX MATCH in this article.
Example Scenario
Imagine you have a table with monthly sales data for different products across various regions.
You need to create a report that shows the amount based on three user-selected criteria:
- Actual or Budget
- Revenue or Profit
- App Name
Using INDEX MATCH
Step 1: Identify the Data Range
First, identify the data range that contains the values you need. In this example, we use the range C22:F31.
Step 2: Find the Row
Use the MATCH function to find the row. For example, to match the app name in H22 with the list of apps in B22:B31:
=MATCH(H22, B22:B31, 0)
Step 3: Find the Column
Combine your criteria (Actual/Budget and Revenue/Profit) using the ampersand (&) symbol. Use a helper cell (C19) to combine the criteria:
= C20 & C21
Drag this formula across until cell F19 to combine all criteria.
Step 4: Combine Everything
Now, use the INDEX and MATCH functions together. This step requires two MATCH formulas because you need to match both the row and the column (two-way lookup):
- The first MATCH function finds the row number based on the app name.
- The second MATCH function finds the column number based on the combined criteria.
=INDEX(C22:F31, MATCH(H22, B22:B31, 0), MATCH(I20&I21, C19:F19, 0))
Alternative 1: Using Array Formulas (CSE)
Handle arrays directly in the MATCH function without helper cells using an array formula. This method requires pressing Ctrl + Shift + Enter (CSE) to confirm the formula.
- Change the MATCH formula to handle the array by combining criteria directly in the function.
=INDEX(C22:F31, MATCH(H22, B22:B31, 0), MATCH(I20&I21, C20:F20&C21:F21, 0))- Instead of pressing ENTER, press Ctrl + Shift + Enter to enter the formula as an array formula. This tells Excel to treat the formula as an array, allowing it to handle multiple criteria.
Excel will display the formula in curly braces {} to indicate it’s an array formula.
💡 Typing the curly braces {} yourself in the formula will not work. You must press Ctrl + Shift + Enter to create the array formula correctly.
Alternative 2: Using Two INDEX Functions
Avoid using helper cells or array formulas (Ctrl + Shift + Enter) by nesting INDEX functions to handle the array internally.
- Use an INDEX function to create the combined criteria array within the formula.
- Nest this INDEX function within your MATCH function to handle the combined criteria.
=INDEX(C22:F31, MATCH(H22, B22:B31, 0), MATCH(I20&I21, INDEX(C20:F20 & C21:F21, 0), 0))
Download the Workbook
Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master the INDEX MATCH formula with multiple criteria in Excel with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.
Featured Bundle
Black Belt Excel Bundle
Here’s Part 1 of this guide on INDEX & MATCH.
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
