# Lookups with several criteria (Excel lesson 16 of 45)

> When one column is not enough to identify a row, match two or more at once.

Source: https://spreadsheetpractice.com/learn/multi-criteria-lookups · Updated October 29, 2026

- **Course:** [Excel course](https://spreadsheetpractice.com/learn), track "Lookups"
- **Time:** about 10 minutes, 3 graded exercises
- **Functions:** `XLOOKUP`, `&`, `INDEX`, `MATCH`
- **Access:** Pro (the lesson text is free; the graded exercises need Pro)
- **Key formula:** `=A2&"|"&B2`: Helper key from two columns

## How it works

A price list is often keyed by more than one thing: region *and* plan, product *and* size, employee *and* month. A lookup on one column returns the first row that matches, which is usually the wrong one.

The simplest fix is a **helper key**: a column that joins the criteria with `&`, such as `=A2&"|"&B2` giving `East|Pro`. Then look up the same joined text. The separator keeps `A|BC` and `AB|C` from colliding.

You can skip the helper column. Excel builds the joined list in memory when you give a lookup an array expression: `XLOOKUP(G2&"|"&G3, A2:A9&"|"&B2:B9, C2:C9)`. The older version of the same idea is `INDEX(C2:C9, MATCH(1, (A2:A9=G2)*(B2:B9=G3), 0))`, where each condition becomes 1 or 0 and only the row with both ones matches.

## Syntax

| Formula | What it does |
|---|---|
| `=A2&"\|"&B2` | Helper key from two columns |
| `=XLOOKUP(G2&"\|"&G3,D2:D9,C2:C9)` | Look up the joined key |
| `=XLOOKUP(G2&"\|"&G3,A2:A9&"\|"&B2:B9,C2:C9)` | No helper column |
| `=INDEX(C2:C9,MATCH(1,(A2:A9=G2)*(B2:B9=G3),0))` | Works in every Excel version |

## Watch out for

- Join with a separator that never appears in the data. Without one, "A1" & "1" and "A" & "11" look identical.
- The criteria must be joined the same way on both sides: same order, same separator.
- A multi-criteria match that finds nothing returns #N/A. Wrap it in IFNA to show a friendly message.

## The exercises

Solve them in the live grid at [Lookups with several criteria](https://spreadsheetpractice.com/learn/multi-criteria-lookups). Each answer is graded, then re-checked on changed data so typed-in numbers don't pass.

1. **Build the key.** In D2, join the region and the plan with a vertical bar, like East|Pro. Fill down to D9.
2. **Look up by key.** In G4, return the price for the region in G2 and the plan in G3 by looking up the joined key in D2:D9.
3. **No helper column.** Clear the key idea from your head: this sheet has no key column. In G4, return the price for the region in G2 and the plan in G3 with one formula that builds the keys itself.

Previous: [Tiered lookups & IFERROR](https://spreadsheetpractice.com/learn/tiered-lookups-iferror) · Next: [Last match, nearest value & wildcards](https://spreadsheetpractice.com/learn/xlookup-search-modes)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
