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

### 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 is a Microsoft MVP and qualified actuary with over 15 years of experience. He has worked in a variety of industries, including insurance, ad tech, and most recently Power Platform consulting. He is a keen problem solver and has a passion for using technology to make businesses more efficient.

### Subscribe

Subscribe for awesome Microsoft Excel videos 😃

👉 Find out more about our Advanced Formulas course!

## Related Posts

#### 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

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

#### Amazing Excel Tips and Tricks

A compilation of the best Excel tips and tricks ranging from beginner to pro that will save you time and make you more productive.

read more

# 6 Comments

1. 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?

• 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.

• 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.

• Good to hear!

2. 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.

• 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 Microsoft Excel Tips

### Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃