TABLE OF CONTENTS
Get started

INDEX MATCH with Multiple Criteria

Learn the easy way to perform an INDEX MATCH with multiple criteria in Excel.

3 minute read
INDEX MATCH Multiple Criteria Example

Avoid using unnecessarily complex and outdated array formulas to perform an INDEX MATCH using multiple criteria. In this guide, we’ll show you how to use INDEX MATCH on multiple criteria using the ampersand (&) sign in Excel.

INDEX MATCH with Multiple Criteria Excel Formula

=INDEX([array], MATCH([X]&[Y], [array]&[array],0))

To perform an INDEX MATCH with multiple criteria in Excel, simply use an ampersand (&) to place multiple references in your lookup value and lookup array inputs in the MATCH formula.

6 Steps to Performing an INDEX MATCH with Multiple Criteria:

  1. Initiate the INDEX function and select the base lookup array.
  2. If searching with row criteria, initiate the MATCH function and input a lookup value, lookup array, and match type.
  3. If using multiple row criteria, use an ampersand (&) to input secondary lookup values and lookup arrays.
  4. If searching with column criteria, initiate the MATCH function and input a lookup value, lookup array, and match type.
  5. If using multiple column criteria, use an ampersand (&) to input secondary lookup values and lookup arrays.
  6. Close the formula and enter.

The following examples will cover several different multiple criteria lookup scenarios that you might find helpful:

  • Multiple Row Criteria INDEX MATCH Lookup
  • Multiple Column Criteria INDEX MATCH Lookup
  • Advanced Multiple Row & Column INDEX MATCH Lookup

Example 1: Multiple Row Criteria INDEX MATCH Lookup

Example INDEX MATCH multiple row criteria

This example spreadsheet contains a basic beverage sales table that shows the units sold by beverage type, location, and invoice date. In this particular example, we’ll show you how to search for a specific row value (units sold) based on 2 different row criteria (beverage name and location).

Step-by-step instructions:

  1. Initiate the INDEX function and select the base lookup array, which in this case is the column containing the units sold (F3:F11).
  2. Initiate the MATCH function in the row number input.
  3. Input both the beverage lookup value (I2) and the location lookup value (I3) within the same lookup input slot and use the ampersand (&) sign to separate the values.
  4. Input both the beverage lookup array (E3:E11) and the location lookup array (D3:D11) in the same lookup array input slot and use the ampersand (&) sign to separate the arrays. Make sure the arrays are input in the same order as the lookup value inputs.
  5. Enter zero for an exact match.
  6. Skip the column number input in the INDEX formula (since we have no column criteria in this example) and close the formula.
Example INDEX MATCH multiple row criteria formula

Example 2: Multiple Column Criteria INDEX MATCH Lookup

Example INDEX MATCH multiple column criteria

This example spreadsheet breakdowns a company’s sales by historic month and year. In this particular example, we’ll show you how to search for a specific column value (sales) based on 2 different column criteria (year and month).

Step-by-step instructions:

  1. Initiate the INDEX function and select the base lookup array, which in this case is the row containing the values (B4:J4).
  2. Skip the row number input since there are no row criteria in this example.
  3. Initiate the MATCH function in the column number input.
  4. Input both the year lookup value (M3) and the month lookup value (M4) within the same lookup input slot and use the ampersand (&) sign to separate the values.
  5. Input both the year lookup array (B2:J2) and the location lookup array (B3:J3) in the same lookup array input slot and use the ampersand (&) sign to separate the arrays. Make sure the arrays are input in the same order as the lookup value inputs.
  6. Enter zero for an exact match.
  7. Close the INDEX formula and hit enter.
Example INDEX MATCH multiple column criteria formula

Example 3: Advanced Multiple Row & Column INDEX MATCH Lookup

Example INDEX MATCH multiple row and column criteria

This example spreadsheet breakdowns product sales by snack type, team, and month. In this particular example, we’ll show you how to search for a specific table value (sales) based on multiple criteria in both the rows and columns.

Step-by-step instructions:

  1. Initiate the INDEX function and select the base lookup array, which in this case is the table containing the sales values (C4:K8).
  2. Initiate the MATCH function in the row number input.
  3. Input the snack lookup value (N3), the snack lookup array (B4:B8), and zero for an exact match. This is a simple one-criteria MATCH setup.
  4. Initiate the MATCH function in the column number input.
  5. Input both the team lookup value (N3) and the month lookup value (N4) within the same lookup input slot and use the ampersand (&) sign to separate the values.
  6. Input both the team lookup array (C2:K2) and the month lookup array (C3:K3) in the same lookup array input slot and use the ampersand (&) sign to separate the arrays. Make sure the arrays are input in the same order as the lookup value inputs.
  7. Enter zero for an exact match.
  8. Close the INDEX formula and hit enter.
Example INDEX MATCH multiple row and column criteria formula

Additional Resources

Looking to learn more about Excel? Consider checking out our Excel for Business & Finance Course to learn all the fundamental Excel skills practiced at Fortune 500 companies around the world! Use this course to join our students who have landed roles at Goldman Sachs, Tesla, Amazon, and other top-tier companies.

Other Articles You Might Find Helpful

Introduction

Building a cash flow statement from scratch using a company income statement and balance sheet is one of the most fundamental finance exercises commonly used to test interns and full-time professionals at elite level finance firms.

Test hyperlink

Image caption goes here
Sample Image Insertion
Dolor enim eu tortor urna sed duis nulla. Aliquam vestibulum, nulla odio nisl vitae. In aliquet pellentesque aenean hac vestibulum turpis mi bibendum diam. Tempor integer aliquam in vitae malesuada fringilla.

Elit nisi in eleifend sed nisi. Pulvinar at orci, proin imperdiet commodo consectetur convallis risus. Sed condimentum enim dignissim adipiscing faucibus consequat, urna. Viverra purus et erat auctor aliquam. Risus, volutpat vulputate posuere purus sit congue convallis aliquet. Arcu id augue ut feugiat donec porttitor neque. Mauris, neque ultricies eu vestibulum, bibendum quam lorem id. Dolor lacus, eget nunc lectus in tellus, pharetra, porttitor.

  • Test Bullet List 1
  • Test Bullet List 2
  • Test Bullet List 3
"Ipsum sit mattis nulla quam nulla. Gravida id gravida ac enim mauris id. Non pellentesque congue eget consectetur turpis. Sapien, dictum molestie sem tempor. Diam elit, orci, tincidunt aenean tempus."

Tristique odio senectus nam posuere ornare leo metus, ultricies. Blandit duis ultricies vulputate morbi feugiat cras placerat elit. Aliquam tellus lorem sed ac. Montes, sed mattis pellentesque suscipit accumsan. Cursus viverra aenean magna risus elementum faucibus molestie pellentesque. Arcu ultricies sed mauris vestibulum.

‍

Conclusion

Morbi sed imperdiet in ipsum, adipiscing elit dui lectus. Tellus id scelerisque est ultricies ultricies. Duis est sit sed leo nisl, blandit elit sagittis. Quisque tristique consequat quam sed. Nisl at scelerisque amet nulla purus habitasse.

Nunc sed faucibus bibendum feugiat sed interdum. Ipsum egestas condimentum mi massa. In tincidunt pharetra consectetur sed duis facilisis metus. Etiam egestas in nec sed et. Quis lobortis at sit dictum eget nibh tortor commodo cursus.

Odio felis sagittis, morbi feugiat tortor vitae feugiat fusce aliquet. Nam elementum urna nisi aliquet erat dolor enim. Ornare id morbi eget ipsum. Aliquam senectus neque ut id eget consectetur dictum. Donec posuere pharetra odio consequat scelerisque et, nunc tortor.
Nulla adipiscing erat a erat. Condimentum lorem posuere gravida enim posuere cursus diam.

Michael Quach
Michael Quach
Senior Instructor

Ready to Level Up Your Career?

Learn the practical skills used at Fortune 500 companies across the globe.

Get started