Two Ways to Set Up Multi-Criteria Lookup Formulas in Excel

The Excel Table below illustrates a common type of lookup problem…perhaps taken to a slight extreme.

Here, we have a specific manager for each month in each region for each product.

So how do we set up a lookup formula that returns the name of the manager for a given date, region, and product?

Strictly speaking, we’re looking for a three-criteria lookup formula in this case. But the approach I’m going to show you works with any number of criteria you might need.

Note that if column E were to contain numbers rather than text, we could use the SUMIFS function to return the value…assuming there were no duplicates.

But because SUMIFS won’t return text, we need to use another approach…

Set Up Your List Boxes

Let’s begin by setting up cells with dropdown list boxes, so that it’s easy to select the items we want.

The first step is to set up the sorted, unique lists shown in rows 3 and 4.

The second step is to set up the dropdown list boxes in the yellow cells. The article How to Set Up a Data-Validation List Box in Excel shows you how to this.

Multi-Criteria Lookup, Version 1

The general approach for the first version is to turn a multi-criteria lookup into a single-criteria lookup. To do so, we first add a calculated column to the Table, a column that combines all the criteria ranges into one.

This image shows both versions:

Column F is the calculated column used by Version 1. As I usually do with calculated columns in Tables, I assigned a dark gray fill to the column’s title cell to remind me that the column contains formulas.

In a sense, setting up this multi-criteria lookup column is a form of “cheating.” However, if you have a large Table with many criteria, this version calculates more quickly than the second version, because the lookup formula has less work to do.

To see how this works, here is the formula for the cell shown at the top of the Lookup column:

F3:   =TEXT(B3,”mmm-yy”)&”|”&C3&”|”&D3

You also could use…

F3:   =TEXT([@Date],”mmm-yy”)&”|”&[@Region]&”|”&[@Product]

…which is longer, but more informative.

The cell that performs the actual multi-lookup uses this INDEX–MATCH formula:

J9:   =INDEX(MyTable[Manager], MATCH(TEXT(Date,”mmm-yy”)&”|”&Region&”|”&Product,MyTable[Lookup],0))

As you can see, the MATCH function begins by assembling the same three-piece item that the Lookup column uses in the Table. But it does so using the three values specified in the yellow cells. Then MATCH looks up that text in the Lookup column of the Table. Because I used a match-type of zero, MATCH uses an exact match and doesn’t care whether the column is sorted.

Finally, after MATCH returns the row number where the three-piece item is found, the INDEX function returns the manager’s name from that row.

Multi-Criteria Lookup, Version 2

This approach to a multi-criteria lookup uses an array formula, and it doesn’t use the Lookup column at all.

Type the following formula in the cell show, and then array-enter the formula. That is, after you type the formula, hold down Ctrl and Shift, then press Enter.

J10:    =INDEX(MyTable[Manager], MATCH(1,(MyTable[Date]=Date)*(MyTable[Region]=Region)*(MyTable[Product]=Product),0))

The second argument in the MATCH function does most of the work. Specifically…

1. For each cell in the Date column it returns TRUE when a date value equals the date criteria you specify. Then, row-by-row, it multiplies this column of TRUE and FALSE values by the next column of tests.
2. For each cell in the Region column, the formula returns TRUE when a Region equals the region value you specify. Then, row-by-row, it’s multplied by the column of date tests, which returns a column of ones (where TRUE is multiplied by TRUE), and zeros otherwise. Then, row-by-row, it multiples this column of ones and zeros by the next column of tests.
3. For each cell in the Product column, the formula returns TRUE when a Product equals the product value you specify. Then, row-by-row, this column of product tests is multiplied by the column of ones and zeros. Here, when a value of TRUE is multiplied by a value of one (1), the result equals one. Otherwise the value equals zero.

Then, after MATCH sets up the final column of ones and zeros in memory, it looks up the first occurrence of 1 (one) in the column, which returns the row index where the 1 was found. And then finally, the INDEX function returns the manager’s name for this row index position.

You can see why Version 1 calculates more quickly…because that version does a lot less work!