Every lookup so far has assumed a luxury: one column that identifies the row. Real tables are frequently messier — the price depends on product and month; the employee is identified by name and site (two John Murphys, different depots). One condition can’t find the row, and stage four’s final trick is looking up on several — which Excel does three honest ways.

Way one: the ampersand key

XLOOKUP matches one array — so make one array that carries both conditions, by gluing on the fly:

=XLOOKUP([@Product] & "|" & [@Month],
         Prices[Product] & "|" & Prices[Month],
         Prices[GBP])

Both sides concatenate into Widgets|Jul, and the lookup is ordinary again. The | separator matters more than it looks: without it, "AB"+"C" and "A"+"BC" both glue to "ABC" — two different rows, one key. Any character that can’t appear in the data works; the pipe is tradition.

This is also the pattern to store: a key column in the data (=[@Product] & "|" & [@Month]) makes the join visible, reusable and debuggable — the same helper-key move you used on messy names, now used for composite ones.

Way two: multiply the conditions

The SUMPRODUCT worldview — conditions are 1s and 0s — gives a glue-free version:

=XLOOKUP(1, (Prices[Product]=[@Product]) * (Prices[Month]=[@Month]),
         Prices[GBP])

Each condition is an array of 1/0; multiplied, only the row passing both holds a 1; look up the 1. Reads oddly the first time, then becomes a favourite — no helper, * for AND, + for OR, and conditions that aren’t equality at all ((Dates>=start) * (Dates<=end) finds the row in a range, which the ampersand can’t).

product=Widgets × month=Jul = the row 1 0 1 0 × 0 1 1 0 = 0 0 1 0 ← XLOOKUP(1, …) finds the only row that passed both AND is multiplication; OR is addition — logic as arithmetic, one more time
Each condition votes 1 or 0; multiplication keeps only the row that got every vote. The lookup then just fetches the 1.

Way three: FILTER, when “the row” might be rows

Both patterns above assume exactly one match. When several rows can legitimately pass — all of Widgets’ July transactions — the honest tool is FILTER with the same multiplied conditions, spilling every match. And when the question is really “what’s the total for product and month”, skip lookups entirely: SUMIFS was built for exactly that. Choosing between them is the stage-four maturity test: lookup = fetch one fact · FILTER = fetch the rows · SUMIFS = aggregate them. Most “multi-criteria lookup problems” in the wild are secretly the third.

The traps, known in advance

Duplicate composite keys are the big one: if Widgets|Jul appears twice, every method silently returns the first — right by luck, wrong by silence. Guard it with a checks-row line: =ROWS(keys)=ROWS(UNIQUE(keys)). Both key columns need the same hygiene as any join — trim both sides, mind text-numbers in one table meeting real numbers in the other. And on very large sheets, the array patterns cost more than plain lookups — a stored key column computes once, versus glue rebuilt in every formula; past a few related tables, that’s the doorstep of the data model, where relationships make the whole question disappear.

Glue for the simple case, multiply for the flexible one, FILTER when many rows may answer, SUMIFS when it was aggregation all along. Four tools, one question first: what, exactly, am I asking for? — which was always the real stage-four skill.