Quick, clean, and to the point

INDEX and MATCH with multiple criteria

Excel formula: INDEX and MATCH with multiple criteria
Generic formula 

To lookup values with INDEX and MATCH, using multiple criteria, you can use an array formula. In the example shown, the formula in H8 is:


Note: this is an array formula, and must be entered with control + shift + enter, except in Excel 365.


This is a more advanced formula. For basics, see How to use INDEX and MATCH.

Normally, an INDEX MATCH formula is configured with MATCH set to look through a one-column range and provide a match based on given criteria. Without concatenating values in a helper column, or in the formula itself, there's no way to supply more than one criteria.

This formula works around this limitation by using boolean logic to create an array of ones and zeros to represent rows matching all 3 criteria, then using MATCH to match the first 1 found. The temporary array of ones and zeros is generated with this snippet:


Here we compare the item in H5 against all items, the size in H6 against all sizes, and the color in H7 against all colors. The initial result is three arrays of TRUE/FALSE results like this:


Tip: use F9 to see these results. Just select an expression in the formula bar, and press F9.

The math operation (multiplication) transforms the TRUE FALSE values to 1s and 0s:


After multiplication, we have a single array like this:


which is fed into the MATCH function as the lookup array, with a lookup value of 1:


At this point, the formula is a standard INDEX MATCH formula. The MATCH function returns 3 to INDEX:


and INDEX returns a final result of $17.00.

Array visualization

The arrays explained above can be difficult to visualize. The image below shows the basic idea. Columns B, C, and D correspond to the data in the example. Column F is created by the multiplying the three columns together. It is the array handed off to MATCH.

INDEX and MATCH with multiple criteria - array visualization

Non-array version

It is possible to add another INDEX to this formula, avoiding the need to enter as an array formula with control + shift + enter:


The INDEX function can handle arrays natively, so the second INDEX is added only to "catch" the array created with the boolean logic operation and return the same array again to MATCH. To do this, INDEX is configured with zero rows and one column. The zero row trick causes INDEX to return column 1 from the array (which is already one column anyway).

Why would you want the non-array version? Sometimes, people forget to enter an array formula with control + shift + enter, and the formula returns an incorrect result. So, a non-array formula is more "bulletproof". However, the tradeoff is a more complex formula.

Note: In Excel 365, it is not necessary to enter array formulas in a special way.

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.