Performing a lookup based on a single value is a fundamental skill, but real-world data is rarely that simple. Often, you need to find information based on two, three, or even five different conditions. While the standard VLOOKUP is limited to a single search term and a single column, the combination of INDEX and MATCH offers a robust solution for complex data retrieval.

For those looking for an immediate solution, the generic array formula for multiple criteria is: =INDEX(return_range, MATCH(1, (criteria1=range1) * (criteria2=range2), 0))

However, implementing this correctly requires an understanding of how Excel processes logic. This guide breaks down the most effective methods to handle multiple criteria, ranging from classic array formulas to the modern XLOOKUP.

The Logic Behind Multiple Criteria Lookups

To master multi-criteria lookups, one must understand Boolean Logic in Excel. When you compare a cell to a value (e.g., A2="Apple"), Excel returns either TRUE or FALSE. In mathematical operations, Excel treats TRUE as 1 and FALSE as 0.

When we use the asterisk symbol (*) between two criteria, we are performing an "AND" operation.

  • TRUE * TRUE = 1
  • TRUE * FALSE = 0
  • FALSE * FALSE = 0

By multiplying ranges together, such as (Range1=Criteria1) * (Range2=Criteria2), Excel generates an array of 1s and 0s. A 1 only appears in the position where every condition is met. The MATCH function then searches for the number 1 within that resulting array to find the correct row index.

Method 1: The Classic INDEX MATCH Array Formula

The classic approach involves creating a virtual array within the MATCH function. This method is compatible with almost all versions of Excel, including legacy versions like Excel 2010, 2013, and 2016.

The Formula Structure