INDEX MATCH with multiple criteria
So, you're an
Fortunately, there is a solution. We can combine
Defining the problem
The spreadsheet below lists SnackWorld sales of both Cookies and Brownies by month. The spreadsheet is in what we call flat-file format, meaning that each separate combination of item category-month is on its own row.
We want to be able to look up the number of units sold based on a particular combination of item-month — for example, the number of Cookies sold in February.
MATCH with multiple criteria
To solve this problem, we'll have to figure out a way to use the
MATCH( lookup_value_1& lookup_value_2, lookup_array_1& lookup_array_2, match_type)
It's very important to note that when you use an array formula like this one, you'll need to commit your formula using Ctrl+Shift+Enter rather than just pressing Enter. This will tell Excel that you're using an array formula rather than a standard formula. To show you that it's recognized an array formula, Excel will put a set of curly braces (
Let's take a look at an example, in which we match against two separate columns:
MATCH( "February"& "Brownies", B3:B8& C3:C8, 0)}
In this formula, we've used the
Note that the order of our criteria here is important. Since our argument
If you're getting an error when you enter the formula, make sure you've commited with Ctrl+Shift+Enter and see those curly braces in the formula bar. Excel will give you an error if you haven't explicitly told it that you're entering an array formula.
Putting it all together
Now that we know how to use
INDEX( range, MATCH( lookup_value_1& lookup_value_2&..., lookup_range_1& lookup_range_2&..., match_type))}
First, let's use this function to find out which month we sold 76 million units worth of Brownies:
INDEX( B3:B8, MATCH( "Brownies"& 76, C3:C8& D3:D8, 0))}
Next, let's create some dynamic input cells that let us input a month and item, then write a formula that tells Excel to pull the number of units sold for that given combination. Our
INDEX( D3:D8, MATCH( G2& G3, B3:B8& C3:C8, 0))}
There we have it —
Save an hour of work a day with these 5 advanced Excel tricks
Work smarter, not harder. Sign up for our 5-day mini-course to receive must-learn lessons on getting Excel to do your work for you.
- How to create beautiful table formatting instantly...
- Why to rethink the way you do VLOOKUPs...
- Plus, we'll reveal why you shouldn't use PivotTables and what to use instead...