How To Use Index And Match To Look Up Data With Multiple Criteria

2016-11-20

Get The Completed Workbook

Looking up a value based on one criteria

 
Let’s say we have a table with employees and their total sales and we want a formula to return the total sales based on the employee.

 

 

Now, if we want to get Allison’s sales, we could use a VLOOKUP formula using this formula.

 

=VLOOKUP(B7,A2:B5,2,FALSE)

 

Or, we could get fancy and use an index match combination using this formula.

 

=INDEX(B2:B5,MATCH(B7,A2:A5,0))

 

Looking up a value with a row criteria and a column criteria

 

 

Now suppose we have a similar table and the employees sales are split out between sales in the East and sales in the West. Now let’s say we need to lookup Guy’s sales for the West.

 

=INDEX(B2:C5,MATCH(C7,A2:A5,0),MATCH(C8,B1:C1,0))

 

Looking up a value with two or more row criteria

 

 

Ok, what happens now if we have the same data but it’s not laid out in a two dimensional range like above (ie one lookup value going down the rows and one lookup value going across the columns)? Let’s say we have a column with the region and we’re still tasked with finding Guy’s sales for the West.

 

 

=INDEX(C2:C9,MATCH(C11&C12,A2:A9&B2:B9,0))

 

In this case we can concatenate our lookup values and lookup ranges to use an array formula.

 

Enter formula with Ctrl + Shift + Enter

 

When you enter this formula, instead of pressing enter in the usual way, you will need to press Ctrl + Shift + Enter. If you’ve done this correctly, then you should see a set of { } curly parenthesis around the formula in the formula bar.

 

If we have 3rd, 4th etc… criteria in additional columns, we can use this same array function approach to lookup values.

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.

Subscribe

Related Articles

37 Awesome Excel Mouse Tips & Tricks You Should Know

37 Awesome Excel Mouse Tips & Tricks You Should Know

While the keyboard is generally quicker, you shouldn’t completely ignore the mouse. There are also some great time saving mouse shortcuts as well. In this post we’ll take a look at some of the best Excel mouse time saving tips and tricks.

read more
25 Amazing Power Query Tips and Tricks

25 Amazing Power Query Tips and Tricks

Power query is amazing tool that allows you to import and transform data with ease and helps to create repeatable and robust procedures with your data. Here are some tips and tricks to help you get the most out Power Query.

read more

Comments

6 Comments

  1. Excel IN TRAINING

    I am trying to use INDEX and MATCH with multiple criteria and I keep getting a formula error message. In the “result” cell, I want to have the formula reference my set of data (located on another sheet), then match to a value in the row above (in the same column as the “result” cell) as well as match to a value in the column to the left (on the same row as the “result” cell) and return the “result” value. So two criteria are being reference and as you copy the formula across the sheet, the “row above” reference changes because you are in a new column, but the “column to the left” reference stays the same. And vice versa as you copy the formula down the sheet. How do I do this with INDEX and MATCH?

    • John

      Sounds like an issue with setting the correct absolute or relative range reference. Try highlighting the various ranges and press F4 to cycle through the different referencing. Try them out until you get something that’s working correctly, unfortunately I can’t see what the issue is to help out any more.

      • Excel IN TRAINING

        Thanks John. I was able to figure it out as I changed my MATCH lookup ranges to be a “vertical” lookup first, followed by a “horizontal” lookup. This worked and I’m good to go. Thanks for your input.

        • John

          Good to hear!

  2. Stefanie

    I am attempting to use INDEX-MATCH with multiple criteria; but I’m running into an issue when I copy the formula to the next column. It seems to have something to do with the brackets, but I cannot figure it out…when I try to copy the formula down, I get an error message that says “You can’t change part of an array.”
    I’m new to using INDEX-MATCH, so I’m just totally confused.

    Thanks for your help.

    • John

      I think you may be trying to paste over the copied cell. Try copying the cell, then moving the range off that cell and then pasting.

Get The Latest News

Follow Us

Follow us on social media to stay up to date with the latest tips in Excel!

Pin It on Pinterest