# 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

With

=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 )}

Output:4

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 ))}

Output:"February"

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 ))}

Month input:"March" (G2 )

Item input:"Cookies" (G3 )

Output:29

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