Multi-Criteria Lookups With INDEX-MATCH

Sometimes a single column does not uniquely identify a row. You might need the price for a product in a specific size, or the salary for an employee in a particular department.

That calls for a multi-criteria lookup: matching on two or more columns at once to pinpoint exactly one row.

INDEX-MATCH handles this elegantly by combining the conditions into a single match test, no extra helper columns required.

The simplest mental model joins your key columns into one. Add a helper column that glues product and size together, then do an ordinary lookup against it.

For example a helper cell might hold =A2&"|"&B2, producing "Shirt|Large". You then MATCH "Shirt|Large" against that combined column.

This works, but it clutters your sheet. The next scenes show how to skip the helper entirely.

The core trick: multiply the two condition tests together inside MATCH.

(A2:A10=G1) gives an array of TRUE/FALSE for the first criterion. (B2:B10=G2) does the same for the second. Multiplying them, (A2:A10=G1)*(B2:B10=G2), yields 1 only where both are TRUE and 0 elsewhere.

MATCH then looks for the value 1 to find the row meeting both conditions.

In spreadsheets TRUE behaves as 1 and FALSE as 0. Multiplying two of these mimics a logical AND:

• 1 times 1 = 1 (both conditions met)
• 1 times 0 = 0
• 0 times 1 = 0
• 0 times 0 = 0

So only rows where both criteria hold produce a 1. Every other row becomes 0. That single 1 marks the row we want.

Now wrap the multiplied array in MATCH, searching for the exact value 1.

MATCH(1, (A2:A10=G1)*(B2:B10=G2), 0) returns the position of the first row where both conditions are TRUE.

If the matching combination sits in the fourth data row, MATCH returns 4. That position is what INDEX needs to fetch the answer.

Feed that MATCH result into INDEX over the column you actually want, say the price in C2:C10.

The complete formula reads: from C2:C10, return the value at the row where product equals G1 and size equals G2.

This is a true multi-criteria lookup with no helper column and no rearranging of your data.

This formula evaluates arrays of conditions. In modern Excel and Google Sheets you simply press Enter and it works.

In older Excel (before dynamic arrays) you must confirm it as an array formula with Ctrl+Shift+Enter, which adds curly braces. If your result is wrong or shows an error in legacy Excel, that confirmation step is usually the missing piece.

Need three criteria? Just multiply in another test. Suppose you also want to match a color in column D against input G3.

Each extra (range=criterion) factor narrows the result further. Only rows where all conditions are TRUE keep a product of 1; any FALSE turns the whole product to 0.

The pattern scales to as many columns as you need.

Data: A = product, B = size, C = price. You want the price of a "Shirt" in "Large".

• G1 = "Shirt", G2 = "Large".
• The condition arrays produce a 1 only on the Shirt+Large row, say row 4.
• MATCH(1, ..., 0) returns 4.
• INDEX(C2:C10, 4) returns that row's price.

Change either input and the formula re-finds the right row instantly.

Keep these in mind:

• Equal ranges: every condition range and the INDEX column must share the same height.
• No match: if no row meets all criteria, MATCH returns #N/A. Wrap the whole thing in IFERROR.
• Duplicates: if more than one row matches, MATCH returns only the first. Make your criteria specific enough to be unique.

If multiple rows can match and you would rather total their values than fetch one, SUMPRODUCT is a clean alternative to array-entered INDEX-MATCH.

It multiplies the condition arrays by the value column and adds the results, so only rows meeting both criteria contribute. No Ctrl+Shift+Enter is needed because SUMPRODUCT handles arrays natively.

Use INDEX-MATCH to pull a single matching value; use SUMPRODUCT to aggregate across all matches.

Test your multi-criteria lookup knowledge.

For multi-criteria lookups with INDEX-MATCH:

• Multiply condition arrays together: (A=G1)*(B=G2) gives 1 only where all hold (a logical AND).
• MATCH(1, ..., 0) finds that row's position.
• INDEX(returnCol, position) returns the value.

Add more *(range=criterion) factors for extra conditions, keep ranges equal in height, confirm with Ctrl+Shift+Enter in legacy Excel, and guard with IFERROR.