
π This article includes 1 downloadable practice file β
In this article
VLOOKUP gets you started. Real reports need more: the target for a region and a month, the code for a product name (to the left), the last invoice of a rep. Two tools cover all of it.
INDEX + MATCH
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
MATCH finds the position, INDEX returns what is at that position. Because the two ranges are separate, the return column can be anywhere, including to the left.
Two-way lookup
Targets for 4 regions Γ 6 months sit in Targets!B2:G5. The target for West in July:
=INDEX(Targets!B2:G5, MATCH("West", Targets!A2:A5, 0), MATCH("Jul", Targets!B1:G1, 0))
=XLOOKUP("Jul", Targets!B1:G1, XLOOKUP("West", Targets!A2:A5, Targets!B2:G5))
The inner XLOOKUP returns West’s whole row; the outer one picks July from it.
XLOOKUP features VLOOKUP never had
| Need | XLOOKUP |
|---|---|
| Default when not found | =XLOOKUP(A2,Codes,Rates,"Not found") |
| Last match (search from bottom) | =XLOOKUP("Meera",Rep,Amount,,0,-1) |
| Return several columns | =XLOOKUP(A2,Codes,Master!B2:D9) spills 3 values |
| Approximate (slabs) | =XLOOKUP(Income,SlabStart,Rate,,-1) = exact or next smaller |
Common mistakes
- MATCH without the 0. The default is an approximate match on sorted data; on unsorted lists it returns wrong rows silently.
- Ranges of different heights in XLOOKUP give #VALUE!.
- Extra spaces in the lookup value. If an obvious match returns #N/A, try
TRIMon both sides.
Practice
Download this lesson’s workbook below. Targets are in βΉ thousands. Type your formula in the yellow column; the Check column turns green when the result matches, and the Answers sheet has a working formula for every task.
π Practice files for this article
- πLesson 3 practice workbookTargets grid, product master and sales register, 8 lookup tasks.β¬ XLSX Β· 21 KB
Free to use for learning. Files with macros (.bas) are plain text β import them with Alt+F11 β File β Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.