How to Use INDEX and MATCH for Two-Criteria Lookups in Microsoft Excel

Single-column lookups using traditional VLOOKUP or basic MATCH functions fail when records require multi-dimensional evaluation: looking up an employee’s bonus based on both Department and Job Level, or retrieving wholesale pricing based on both Product SKU and Customer Tier. Historically, analysts created messy “helper columns” concatenating attributes together. With modern array evaluation in Microsoft Excel, combining INDEX and MATCH using boolean multiplication provides a clean, robust multi-criteria lookup engine without altering underlying data tables.

The Mechanics of Boolean Array Matching

In Microsoft Excel, logical comparisons return arrays of TRUE and FALSE values. When two boolean conditions are multiplied together using the asterisk (*), Excel performs an intersection (logical AND):

• TRUE * TRUE = 1 (Both criteria matched)
• TRUE * FALSE = 0 (First criteria matched, second failed)
• FALSE * TRUE = 0 (First criteria failed, second matched)
• FALSE * FALSE = 0 (Both criteria failed)

By designing a MATCH function that searches for the numeric value 1 within this multiplied boolean vector, Excel identifies the precise row where all criteria evaluate to true simultaneously.

Building the Multi-Criteria Formula Step by Step

Consider a compensation table where Department names span A2:A100, Job Levels (“Associate”, “Senior”, “Director”) span B2:B100, and Base Salaries are in C2:C100. User search criteria are entered in cells E2 (Target Department) and F2 (Target Level).

Construct the formula in cell G2:

=INDEX($C$2:$C$100, MATCH(1, ($A$2:$A$100=E2) * ($B$2:$B$100=F2), 0))

How Excel resolves this calculation:

1. ($A$2:$A$100=E2) produces a vertical array: {TRUE; FALSE; TRUE; ...}.
2. ($B$2:$B$100=F2) produces a second vertical array: {FALSE; TRUE; TRUE; ...}.
3. Multiplying the arrays yields: {0; 0; 1; ...}.
4. MATCH(1, ..., 0) searches for the first occurrence of 1 with exact match mode (0), returning row index 3.
5. INDEX($C$2:$C$100, 3) retrieves the corresponding salary figure from column C.

Extending to Three or More Simultaneous Conditions

The boolean multiplication paradigm scales infinitely without increasing complexity. To add a third condition (e.g., Office Location in column D matching cell G2):

=INDEX($C$2:$C$100, MATCH(1, ($A$2:$A$100=E2) * ($B$2:$B$100=F2) * ($D$2:$D$100=G2), 0))

Defensive Error Shielding with IFERROR

If an invalid department-level combination is queried, MATCH outputs #N/A. Shield operational templates by enclosing the expression with IFERROR:

=IFERROR(INDEX($C$2:$C$100, MATCH(1, ($A$2:$A$100=E2) * ($B$2:$B$100=F2), 0)), "Record Not Found")

Performance Advantages Over Concatenated Helper Columns

Using boolean INDEX-MATCH eliminates the maintenance debt of auxiliary columns. Calculations evaluate entirely in memory, leverage multi-threaded CPU execution, and remain fully compatible across both modern Microsoft 365 Dynamic Array workbooks and legacy Excel 2016/2019 editions.