Excel lookups: XLOOKUP, INDEX/MATCH and handling missing matches
For office, finance and operations staff who already use basic XLOOKUP and want lookups they can trust in real reports. Prerequisite: the course on lookups, conditional totals and checking spreadsheet data (or equivalent). Covers XLOOKUP's match and search modes, INDEX/MATCH for older Excel versions, what each lookup error means, when to use IFNA instead of IFERROR, and how to reconcile two lists in both directions. Function behaviour follows Microsoft's Excel documentation for Microsoft 365, Excel 2021 and Excel 2024.
- Level
- Intermediate
- Length
- About 85 minutes
- Contents
- 4 lessons · final exam
- Status
- Published · updated 10 Oct 2026
Skills you'll practise
- Write XLOOKUP formulas that use if_not_found, match_mode and search_mode correctly for exact, banded and last-entry lookups
- Write INDEX/MATCH formulas with an exact match type for workbooks that must open in Excel 2016 or 2019
- Identify the cause of #N/A, #VALUE! and wrong-but-plausible lookup results, and choose IFNA or IFERROR knowing what each would hide
- Reconcile two lists in both directions and classify every row as matched, different or missing
Course outline
- 1.XLOOKUP beyond the basicsLesson · 16 min
- 2.INDEX/MATCH and VLOOKUP in shared workbooksLesson · 14 min
- 3.Lookup errors: causes, IFNA and IFERRORLesson · 14 min
- 4.Reconciling two listsLesson · 16 min
- 5.Excel lookups: XLOOKUP, INDEX/MATCH and handling missing matches: knowledge checkKnowledge check · 15 questions
- 6.Excel lookups: XLOOKUP, INDEX/MATCH and handling missing matches: practical exerciseKnowledge check · 1 question
- 7.Final exam8 questions · passing it completes the course, so people who already know the material can test out
Sources it draws on
The lessons and questions are written from these references, so learners can go back to the original.
- Microsoft Support: XLOOKUP function
- Microsoft Support: MATCH function
- Microsoft Support: INDEX function
- Microsoft Support: Look up values with VLOOKUP, INDEX, or MATCH
- Microsoft Support: VLOOKUP function (range_lookup default, troubleshooting)
- Microsoft Support: IFNA function
- Microsoft Support: IFERROR function
See it with your own jobs and topics
Tell us about your team and we'll walk you through setup, from choosing jobs to your first skills check.