WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Index Match Multiple Criteria

Index Match Multiple Criteria

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Index Match Multiple Criteria Rows and Columns

We all use VLOOKUP day in day out to fetch the data, and also we are aware of the fact that VLOOKUP can fetch the data from left to the right, so 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 VLOOKUP function in excel. With advanced technology, we can use these INDEX + MATCH formula to match multiple criteria for rows and columns. So this special article will take you through in detail about this technique.

Index Match Multiple Criteria

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

Here we explain how to use 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

Example #1 – INDEX + MATCH Formula

Not the majority of the excel users lookup functions beyond VLOOKUP, reasons could be so many. Anyways let’s have a simple introduction to this formula before we go 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 the selection we make from the drop-down list sales amount has to appear in cell E2.

The problem is we cannot apply the VLOOKUP formula because the lookup value “Sales Rep” is to the right of the result column “Sales,” so in these cases, we can use combination lookup value formula INDEX + MATCH.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

INDEX+MATCH Formula 1-2

INDEX looks for the mentioned row number value in the range A2:A11, and in this range, we need to provide from which row we need the sales value to come from. This row value is based on the “Sales Rep” name selected in the drop-down list in excel, 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 below one.

Multiple Criteria in INDEX+MATCH Formula 1

We have monthly sales values of “Sales Rep.” From this table, we need dynamic results like in cell A15, I have created a “Sales Rep” drop-down list, and in B14 cell I 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 I 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, in these cases, we need to match both rows and columns. Follow the below steps to apply the formula to match both rows and columns.

Step 1: Open the INDEX function in cell B15.

Multiple Criteria in INDEX+MATCH Formula 1-2

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

Multiple Criteria in INDEX+MATCH Formula 1-3

Step 3: Next argument of INDEX function 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 to dynamically fetch the row number based on the selection open MATCH function.

Multiple Criteria in INDEX+MATCH Formula 1-4

Step 4: LOOKUP VALUE of the MATCH function is “Sales Rep,” so choose A15 cell as the reference.

Multiple Criteria in INDEX+MATCH Formula 1-5

Step 5: Lookup Array will be “Sales Rep” names range in the main table. So choose range as A2 to A11.

Example 1-6

Step 6: Match Type of MATCH function will be exact so enter zero as the argument value.

Example 1-7

Step 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. This is dependent on the month we select from the drop-down list of the cell B14. So to get the column number automatically, open another MATCH function.

Example 1-8

Step 8: This time lookup value will be the month name, so select B14 cell as the reference.

Example 1-9

Step 9: Lookup array will be month range of cells in the main table, i.e., from B1 to G1.

Example 1-10

Step 10: The last argument is match type; choose “Exact Match” as the criteria. Close two brackets and hit the enter key to get the result.

Example 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

  • A combination of INDEX + MATCH can be more powerful than the VLOOKUP formula.
  • INDEX & MATCH can match both rows and columns headers and return the result from the middle table.
  • MATCH can return the row number and column number of the table headers of both rows & columns.

Recommended Articles

This has been a guide to Index Match Multiple Criteria. Here we learn how to use the index+match formula to match multiple criteria in excel and downloadable excel template. You may learn more about excel from the following articles –

  • Excel Match Multiple Criteria
  • Break-Even Analysis Excel
  • Index Match in VBA
  • VLOOKUP with Match in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Index Match Multiple Criteria Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More