XLOOKUP + 10 Tricks: Excel learning resource
XLOOKUP + 10 Tricks
After a quick recap of basic XLOOKUP syntax, Chandoo runs through ten progressively harder lookup problems on one practice table: pulling every column at once (both across a row and stacked as a vert…
What this video actually covers
After a quick recap of basic XLOOKUP syntax, Chandoo runs through ten progressively harder lookup problems on one practice table: pulling every column at once (both across a row and stacked as a vertical list), wildcard "starts with" matching, "greater than or equal to" lookups, matching on more than one condition at a time, looking a value up between two dates, and pulling out the highest matching value in a range instead of a fixed row. The last few tricks cover returning the second matching result instead of the first and getting every match at once as a spilled array, closing with a short bonus on grabbing two different columns in one call.
Where to jump in
What to practice while watching
- Write a single-column XLOOKUP against the practice product table, then extend it to return every column at once.
- Add a wildcard XLOOKUP that matches on the start of a product name instead of an exact one.
- Combine two lookup_value arguments with & to build a multi-condition XLOOKUP (for example product plus region).
- Use the match_mode argument to return the closest value greater-than-or-equal-to a target number.
- Reproduce a "second match" case on the practice sheet and compare the result against a plain single-condition XLOOKUP.
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: Chandoo
- Format: video
- Level: Intermediate
- 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: After a quick recap of basic XLOOKUP syntax, Chandoo runs through ten progressively harder lookup problems on one practice table: pulling every column at once (both across a row and stacked as a vert…
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.
