Index Match Multiple Criteria

Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Index Match Multiple Criteria Rows and Columns

We all use the Excel VLOOKUP function day in and day out to fetch the data. Also, we know that the VLOOKUPVLOOKUPThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more function can bring the data from left to right, so the lookup value should always be on the left side of the result columns. However, we have several alternatives that can be used as an alternative to the VLOOKUP function in Excel. We can use these INDEX + MATCH formulaINDEX + MATCH FormulaThe INDEX function can return the result from the row number, and the MATCH function can give us the position of the lookup value in the array. This combination of the INDEX MATCH is beneficial in addressing a fundamental limitation of VLOOKUP.read more to match multiple criteria for rows and columns with advanced technology. So, this special article will take you through it in detail about this technique.

Index-Match-Multiple-Criteria

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Index Match Multiple Criteria (wallstreetmojo.com)

INDEX MATCH Function Video Explanation

 

How to Use INDEX + MATCH Formula to Match Multiple Criteria?

We explain using the INDEX+MATCH formula to match multiple criteria for rows and columns with examples.

You can download this Index Match Multiple Criteria Excel Template here – Index Match Multiple Criteria Excel Template

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Example #1 – INDEX + MATCH Formula

Not the majority of the Excel users lookup functions beyond the VLOOKUP. The reasons could be so many. Let us briefly introduce this formula before going to the advanced level.

For example, look at the below data structure in Excel.

INDEX+MATCH Formula 1

We have “Sales Rep” names and their respective sales values. On the other hand, we have a drop-down list of “Sale Rep” in cell D2.

INDEX+MATCH Formula 1-1

Based on our selection from the drop-down list “Sales” amount has to appear in cell E2.

We cannot apply the VLOOKUP formulaVLOOKUP FormulaThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more because the lookup value “Sales Rep” is to the right of the result column “Sales.” So in these cases, we can use the combination INDEX + MATCH lookup value formula.

INDEX+MATCH Formula 1-2

The INDEX function looks for the mentioned row number value in the range A2:A11. Next, we need to provide from which row we need the sales value to come in this range. This row value is based on the “Sales Rep” name selected in the drop-down list in excelDrop-down List In ExcelA drop-down list in excel is a pre-defined list of inputs that allows users to select an option.read more, so the MATCH function looks for the “Sales Rep” row number in the range B2:B11 and returns the row number of the matched value.

Example #2 – Multiple Criteria in INDEX + MATCH Formula

Now, we have a data structure like the one below.

Multiple Criteria in INDEX+MATCH Formula 1

We have monthly sales values of “Sales Rep.” From this table, we need dynamic results like cell A15. So, we have created a “Sales Rep” drop-down list. In the B14 cell, we have created a “Month” drop-down list.

Based on the selection made in these two cells, our formula has to fetch the data from the above table.

For example, if we choose “Rep 8” and “Apr,” then it has to show the sales value of “Rep 8” for the month of “Apr.”

Multiple Criteria in INDEX+MATCH Formula 1-1

So, we need to match both rows and columns in these cases.

Follow the below steps to apply the formula to match both rows and columns.

  1. We must first open the INDEX function in cell B15.


    Multiple Criteria in INDEX+MATCH Formula 1-2

  2. The first argument of the INDEX function is “Array,” i.e., from which range of cells we need the result. So, we need sales values in this case, so we must choose the range of cells from B2 to G11.


    Multiple Criteria in INDEX+MATCH Formula 1-3

  3. The next argument of the INDEX function is from which row of the selected range we need the result. In this case, we need to arrive at the “Sales Rep” row number based on the selection made in the cell A15 drop-down cell. So, we must open the MATCH function for dynamically fetching the row number based on our selection.


    Multiple Criteria in INDEX+MATCH Formula 1-4

  4. The lookup value of the MATCH function is “Sales Rep,” so we must choose the A15 cell as the reference.


    Multiple Criteria in INDEX+MATCH Formula 1-5

  5. The lookup array will be the “Sales Rep” named range in the main table. So, we must choose a range from A2 to A11.


    Multiple Criteria in INDEX+MATCH Formula 1-6

  6. The MATCH type of the MATCH function will be exact. So, we must insert zero as the argument value.


    Multiple Criteria in INDEX+MATCH Formula 1-7

  7. The next argument of the INDEX function is “Column Number,” i.e., from the selected range of cells from which column we need the result. It is dependent on the month we choose from the drop-down list of cell B14. So, to get the column number automatically, we must open another MATCH function.


    Multiple Criteria in INDEX+MATCH Formula 1-8

  8. The lookup value will be the month name, so select the B14 cell as the reference.


    Multiple Criteria in INDEX+MATCH Formula 1-9

  9. The lookup array will be the month range of cells in the main table, i.e., B1 to G1.


    Multiple Criteria in INDEX+MATCH Formula 1-10

  10. The last argument is MATCH type. Choose “Exact Match” as the criteria. Close two brackets and press the “Enter” key to get the result.


    Multiple Criteria in INDEX+MATCH Formula 1-11

As we can see above, we have chosen “Rep 6” and “Apr” as the month, and our formula has returned the sales value for the month of “Apr” for “Rep 6”.

Note: Yellow-colored cell is the reference for you.

Things to Remember

This article is a guide to Index Match Multiple Criteria. Here, we learn how to use the INDEX+MATCH formula to match multiple criteria in Excel and a downloadable Excel template. You may learn more about Excel from the following articles: –