Lesson 16 of 45, Lookups
Lookups with several criteria
Quick answer
When one column is not enough to identify a row, match two or more at once.
- Key formula
=A2&"|"&B2(Helper key from two columns)
Watch it done
4 stepsWatch the technique worked out on sample data, then try it yourself in the exercises.
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
=A2&"|"&B2Helper 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 versionWatch 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
- Build the key. In D2, join the region and the plan with a vertical bar, like East|Pro. Fill down to D9.
- 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.
- 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.
Questions people ask about this
Updated Checked by running: every exercise answer on this page was graded by our formula engine on .
Graded practice for this lesson is part of Pro
The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 43 Pro lessons, Excel and Google Sheets, 125 exercises in all.
- Build the key
- Look up by key
- No helper column