Try the example, then check the result.
Download the fictional material and follow the steps below. The video uses English illustrative diagrams; it does not present a live app recording. Product controls may vary by version.
Check the answer before applying the method to your own data. How we review guides ↗
Watch the complete lesson.
The video uses English illustrative diagrams; it does not present a live app recording. English captions. Product menus may differ in your account.
Quick answer. Join SKU and Location into a unique helper key, then XLOOKUP that combined key. In this practice, WX-10 plus West must return $24; SKU alone returns the first East row at $18.
Use the fictional practice file: location-price-practice.csv, plus the independent answer check. Work in a copy. This guide stands on its own without the video.
Scope and limits. The SKU-and-location pair must be unique. A repeated composite key still needs a business rule. XLOOKUP requires a modern Excel version; check the result against both fictional locations.
1. Expose the first-match trap
Read A2:D5. In a spare cell, a SKU-only lookup such as =XLOOKUP("WX-10",A2:A5,D2:D5) demonstrates the first-match $18 result. Microsoft documents first-match behavior; do not treat this as a missing-value error.
Checkpoint: SKU-only first match is East $18.
2. Create a visible combined key
In E1 type Key. In E2 enter =A2&"|"&B2, then fill E2:E5. The four results must be WX-10|East, WX-10|West, AX-20|East, AX-20|West. A delimiter prevents some ambiguous joins; a real dataset still needs a uniqueness check.
Checkpoint: Four distinct composite keys.
3. Look up both fields
Enter WX-10 in G2, West in G3, and =XLOOKUP(G2&"|"&G3,E2:E5,D2:D5,"Not found") in G4. E2:E5 and D2:D5 have identical row counts. The requested pair occurs in the second source row and returns $24.
Checkpoint: G4 shows $24 for West.
4. Change just the location
Switch G3 to East and check $18, then return it to West and check $24. If the result does not change, inspect the helper key and formula references instead of changing prices in the source table.
Checkpoint: West $24; East $18.
Verify the result
E2:E5 should be WX-10|East, WX-10|West, AX-20|East, AX-20|West. With G2=WX-10 and G3=West, =XLOOKUP(G2&"|"&G3,E2:E5,D2:D5,"Not found") returns $24. Change G3 to East and it returns $18. All four composite keys occur once.
If it still does not work
If both locations give $18, check that E2:E5 was filled and G3 is part of the joined search value. If Not found appears, check case, spaces, delimiters, and aligned four-row ranges. If a composite key repeats, resolve the duplicate rather than trusting the first price.
Official sources
- XLOOKUP function (checked 26 October 2026).
- Combine text from two or more cells in Excel (checked 26 October 2026).
Any instructional panel without a marked actual-app capture is an illustrative diagram of the fictional exercise.
Practice downloads.
The exercise files contain invented information. Save a local copy before editing.