How an Excel Pro Uses XLOOKUP (R.I.P. VLOOKUP & INDEX MATCH): Excel learning resource
How an Excel Pro Uses XLOOKUP (R.I.P. VLOOKUP & INDEX MATCH)
Framed as a direct upgrade path from VLOOKUP: Gharani builds one XLOOKUP formula and walks through the specific defaults that make it safer than VLOOKUP — exact match is the default behavior so there…
How an Excel Pro Uses XLOOKUP (R.I.P. VLOOKUP & INDEX MATCH)
Leila Gharani
Open on YouTube (opens in a new tab)What this video actually covers
Framed as a direct upgrade path from VLOOKUP: Gharani builds one XLOOKUP formula and walks through the specific defaults that make it safer than VLOOKUP — exact match is the default behavior so there's no FALSE argument to forget, and the lookup column no longer has to sit to the left of the return column. She then uses the search_mode argument to find the last match in a list instead of the first, useful for something like an employee's most recent department, reuses the approximate-match idea to bucket values into tiers, and shows that XLOOKUP doesn't care whether the source data is sorted. It closes by pointing out XLOOKUP also replaces HLOOKUP for horizontal lookups.
Where to jump in
What to practice while watching
- Rebuild a VLOOKUP you'd normally write against the practice dataset as an XLOOKUP instead, and confirm you no longer need a FALSE or 0 argument for an exact match.
- Set up a lookup where the return column is to the left of the lookup column and solve it directly with XLOOKUP, something VLOOKUP can't do without INDEX/MATCH.
- Use search_mode = -1 to pull the last matching row in the dataset instead of the first.
- Reuse the approximate-match idea to bucket a numeric column, for example quantity or price, into ranges the way the video buckets salaries into bonus tiers.
Exercise file
Practice the steps above on a real workbook instead of a blank sheet — no sign-up required.
Download VLOOKUP / XLOOKUP practice dataset (.xlsx)Recommended learning path
Start by watching the lesson once without pausing, then reopen Excel and rebuild the example with your own small dataset. Save one clean practice workbook before moving to the next topic.
Source details
- Channel: Leila Gharani
- Format: video
- Level: Mixed
- Topic: Formulas & Functions
Go deeper with this skill
Write formulas that are correct, readable, and resilient when the workbook changes. For this article, the goal is to practice: Framed as a direct upgrade path from VLOOKUP: Gharani builds one XLOOKUP formula and walks through the specific defaults that make it safer than VLOOKUP — exact match is the default behavior so there…
Practice workbook setup
Create a small table with clean headers, sample edge cases, and expected outputs you can check manually.
Practice workflow
- Write the business rule in plain English before writing the formula.
- Build the formula in small pieces and test each piece.
- Use absolute references, table references, or named ranges intentionally.
- Test blanks, missing values, duplicate matches, and unexpected text.
Quality checks
- The formula returns the expected result for normal and edge-case rows.
- References point to stable ranges or tables.
- Someone else can understand the formula after reading the labels around it.
Common mistakes
- Fixing one row without testing the rest of the column.
- Using approximate match or partial criteria without meaning to.
- Letting hidden spaces or text numbers cause false mismatches.
Next actions
- Create three test rows that should return different outcomes.
- Compare the result with an alternate formula or manual filter.
Formula focus: even if this workflow is not formula-heavy, add one check cell that confirms the final output still matches the source data.
