Master
XLOOKUP
in Excel
Learn Excel's most powerful lookup function, step by step, with real-life use cases
IN EXCEL
lookup function step-by-step
Still stuck with VLOOKUP?
- Formulas break every time you insert or delete a column
- Can't look up data to the left of your lookup column
- #N/A errors cluttering your reports
- Counting column positions to get the index number right
- Nesting IFERROR just to hide ugly errors
You'll handle it all with one function
- Formulas that survive structural changes to your data
- Search left, right, up, or down, no restructuring needed
- Custom messages when a value isn't found, built-in
- Direct range selection, no index counting ever again
- Wildcard matches for messy, real-world data
Everything you need to actually master XLOOKUP
Not a wall of theory. Each lesson explains one argument, shows a real workplace scenario, and makes you practise it immediately.
The 3 mandatory arguments
lookup_value, lookup_array, return_array, crystal-clear explanations with a real HR employee matching scenario.
Built-in error handling
The [if_not_found] argument eliminates #N/A errors and makes your reports look professional, no IFERROR nesting required.
Wildcard & partial matches
Use match_mode 2 with * wildcards to find values in messy data, even when names are in a different order across lists.
Reverse & two-way lookups
Search in any direction with search_mode, and nest two XLOOKUPs together to match across both rows and columns simultaneously.
The NUMBER trap fix
Wildcards only work on text, learn the TEXT() wrapper trick to make partial searches work on numeric IDs and phone numbers.
5 graded exercises + answer key
From a basic employee lookup to a full two-way nested formula, progressively harder exercises with complete step-by-step solutions.
A structured path from confused to confident
52 focused pages. No filler. Every section builds on the last.
Six arguments. Infinite flexibility.
Most people use only the first three. This guide teaches you all six, and when each one actually matters.
lookup_value, /* required, what to find */
lookup_array, /* required, where to search */
return_array, /* required, what to return */
[if_not_found], /* optional, custom error text */
[match_mode], /* optional, exact/wildcard/range */
[search_mode] /* optional, direction of scan */
)
Scenarios you'll actually encounter at work
Abstract examples don't stick. Every use case in this guide mirrors a situation you'd face in HR, operations, sales, or event management.
Cross-referencing employee lists
You have an HR master roster and a project team list. Use XLOOKUP to instantly flag which employees are available for new assignments, even across 1,350+ rows.
Validating gym membership with partial names
When a member's last name is missing in one list, wildcard match_mode 2 with &"*" syntax catches the partial match and confirms their active status anyway.
Merging email lists from mixed-format exports
Your CRM export has "First Last" but account managers sent "Last, First" lists. XLOOKUP with wildcards matches all of them regardless of name order.
Tiered commission lookup on unsorted data
match_mode -1 finds the correct bonus bracket for a salesperson's earnings, even when the threshold table isn't sorted. No helper columns, no sorting required.
XLOOKUP vs VLOOKUP, side by side
| Feature | ❌ VLOOKUP | ✅ XLOOKUP |
|---|---|---|
| Search direction | Looks right only | Any direction, left, right, up, down |
| Default match type | Approximate (dangerously wrong by default) | Exact match (safe by default) |
| Column selection | Requires column index number (fragile) | Direct range, impossible to break |
| Error handling | Needs IFERROR wrapper | Built-in [if_not_found] argument |
| Column stability | Breaks if columns are inserted or deleted | Dynamic, stays linked even if layout changes |
| Horizontal lookup | Needs separate HLOOKUP function | One function handles both orientations |
| Partial matching | Not supported natively | match_mode 2 with * wildcards |
| Most recent value | Not supported without helper formulas | search_mode -1 scans from the bottom up |
Start using XLOOKUP today.
One payment. Yours forever.
A practical, no-fluff PDF guide you can work through in a single afternoon, and reference forever.