Advanced Excel · Interactive lesson 4 of 12
Advanced lookup patterns
A plain lookup finds a value by one column. Real commission tables, rate matrices, and tiered pricing usually need more than one — region and band, sales volume within a range. These patterns cover both.
Screens shown: Excel for Microsoft 365 for Windows. Practice works in any browser.
Written tutorialAdvanced Lookups in Excel: Multiple Criteria and Approximate MatchSkill check
Try these first:
- Look up a value by matching on two columns at once, not just one.
- Find a value's position in a list with
XMATCH. - Rebuild a lookup with
INDEXandMATCHinstead ofXLOOKUP. - Return a tier or band name for a number that falls between two thresholds, not an exact match.
All familiar? Skip ahead to A5 — Advanced PivotTables.
Concept teach
What you're learning
- Multiple-criteria lookup
- Concatenate the criteria into one key on both sides:
=XLOOKUP(B2&"-"&C2,$F$2:$F$7,$H$2:$H$7)matches Region and Band at once against a helper column built the same way. - Two-way lookup
- The same concatenated-key trick works with
INDEX/MATCHtoo:=INDEX($H$2:$H$7,MATCH(B2&"-"&C2,$F$2:$F$7,0))— same result, useful when you needINDEX's flexibility elsewhere in the sheet. XMATCH=XMATCH(value,range)returns a position, not a value — the building blockINDEXneeds.INDEX+MATCH=INDEX(returnRange,MATCH(value,lookupRange,0))— the two-function version ofXLOOKUP, and still worth knowing since it appears in older workbooks.- Approximate match for bands
MATCH(value,range,1)against an ascending list of thresholds finds the last one the value still clears — exactly what a commission tier or price band needs.
Commission-rate matrix — lives in F2:H7
| F — Key (Region-Band) | H — Rate |
|---|---|
| East-A | 4% |
| East-B | 6% |
| West-A | 5% |
| West-B | 7% |
| Central-A | 3% |
| Central-B | 5% |
Each key is Region and Band concatenated with a dash — built the same way on both sides of the lookup.
Sales-tier bands — lives in J2:K4
| J — Minimum Sales | K — Tier |
|---|---|
| 0 | Bronze |
| 15,000 | Silver |
| 25,000 | Gold |
Where to find it in Excel
Formulas›Lookup & Reference›INDEX · XMATCH
| A | B | C | D | E | F | G | H | ||
|---|---|---|---|---|---|---|---|---|---|
| 1 | Region | Q1 | Q2 | Q3 | Q4 | Lookup | |||
| 2 | North | 120 | 135 | 150 | 160 | Region | South | ||
| 3 | South | 98 | 104 | 117 | 121 | Quarter | Q3 | ||
| 4 | East | 110 | 108 | 125 | 131 | Result | 117 | ||
| 5 | West | 87 | 95 | 99 | 112 | ||||
| 6 | |||||||||
| 7 | |||||||||
| 8 |
Swipe sideways to see the whole window.
- 1INDEX, MATCH, XMATCH and XLOOKUP are under Lookup & Reference.
- 2One XMATCH finds the row, the other the column; INDEX returns where they cross.
- 3The value at South × Q3.
- 4Change these inputs and the result updates.
Rendered view of Excel for Microsoft 365 (Windows). Ribbon layout can differ slightly on Mac, Excel for the web or a narrow window.
Guided build
A commission-rate matrix keyed by Region and Band above. Look up each rep's rate two different ways, then place a rep into a sales tier by approximate match.
Independent extension
Build your own rate matrix
Take any two-dimensional rate or pricing table you actually use — shipping cost by weight and zone, a tax rate by state and category — and build both a concatenated-key XLOOKUP and an equivalent INDEX/MATCH version of the same lookup.
Spot a mistake or have an idea for this lesson? Send feedback