Advanced Lookup Formulas
Two Ways to Set Up MultiCriteria Lookup Formulas in Excel
Here are the only two ways I know to set up formulas that look up data
in an Excel Table, using more than one criteria.
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

(Download the files here.)
This
Excel Table 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?
SStrictly speaking, we’re looking for a threecriteria 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 article
How to Use Advanced Filter to Create a
Sorted, Unique List of Items from an Excel Table shows you how
to do this.
The second step is to set up the dropdown list boxes in the
yellow cells. The article
How to Set Up a DataValidation List
Box in Excel shows you how to this.
MultiCriteria Lookup, Version 1
The general approach for the first version is to turn a
multicriteria lookup into a singlecriteria 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 multicriteria 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,"mmmyy")&""&C3&""&D3
You also could use…
F3: =TEXT([@Date],"mmmyy")&""&[@Region]&""&[@Product]
…which is longer, but more informative.
The cell that performs the actual multilookup uses
this INDEXMATCH formula:
J9: =INDEX(MyTable[Manager], MATCH(TEXT(Date,"mmmyy")&""&Region&""&Product,MyTable[Lookup],0))
As you can see, the
MATCH function begins by assembling the
same threepiece 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 matchtype 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
threepiece item is found, the
INDEX function returns the
manager’s name from that row.
MultiCriteria Lookup, Version 2
This approach to a multicriteria 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
arrayenter 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...
 For each cell in the Date column it returns TRUE when a
date value equals the date criteria you specify. Then,
rowbyrow, it multiplies this column of TRUE and FALSE
values by the next column of tests.
 For each cell in the Region column, the formula returns
TRUE when a Region equals the region value you specify.
Then, rowbyrow, 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, rowbyrow,
it multiples this column of ones and zeros by the next
column of tests.
 For each cell in the Product column, the formula returns
TRUE when a Product equals the product value you specify.
Then, rowbyrow, this column of product tests is multiplied
by the column of ones and zeros. Here, when a value of TRUE
is multplied 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!
Downloads and Further Reading
You’ll find related content here:
And also, I created one download file for all three articles.
You can
download the files at this link.
Tags: #multicriteria lookups, #text function, #index
function, #match function, #tables
