Excel XLOOKUP Function (Modern Lookup) – Examples & Practice
Practice the Excel XLOOKUP function online with an interactive grid, instant feedback, and clear formula help.
Instruction
Look up code "P-102" in A2:A6 and return the matching price from C2:C6; show "Not found" if missing.
Formula Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- lookup_value: The value to search for.
- lookup_array: The array or range to search.
- return_array: The array or range to return values from.
- [if_not_found]: Optional value if no match is found.
- [match_mode]: Optional exact, next smaller, next larger, or wildcard behavior.
- [search_mode]: Optional first-to-last or last-to-first search.
What it does
XLOOKUP finds a value in a lookup range and returns the corresponding value from a return range. It is the modern replacement for many VLOOKUP/HLOOKUP patterns with fewer structural constraints.
Excel XLOOKUP Function Examples
Exact match with fallback
=XLOOKUP("P-102", A2:A6, C2:C6, "Not found")
Returns the price from C when the code exists in A; otherwise returns the text Not found.
Horizontal spill of a row
=XLOOKUP("North", B1:E1, B3:E3)
Looks up a region label in a header row and returns the matching data row when shapes align.
Last match search mode
=XLOOKUP("R-01", A2:A20, B2:B20, , , -1)
Useful when you want the latest occurrence and your Excel build supports the search_mode argument.
lookup-reference-table.xlsx
| A | B | C | |
|---|---|---|---|
| 1 | Code | Name | Price |
| 2 | P-101 | Mouse | 19 |
| 3 | P-102 | Keyboard | 45 |
| 4 | P-103 | Monitor | 199 |
| 5 | P-104 | USB Hub | 29 |
| 6 | P-105 | Headset | 59 |
| 7 | Output |
Input Formula
Need Help?
Tips
- Name lookup_array and return_array together as one pair in documentation.
- Prefer explicit match_mode when sharing files with mixed Excel versions.
- Pair with **FILTER** when you need all matches, not just one.
XLOOKUP Function Use Cases
- Replace VLOOKUP with clearer “not found” defaults
- Left lookups without array gymnastics
- Approximate tier tables with explicit match_mode
- Return entire result rows in dynamic Excel
- Simpler onboarding for new analysts
Common mistakes - XLOOKUP function not working
- Mismatched heights between
lookup_arrayandreturn_array - Omitting
if_not_foundand still seeing #N/A in dashboards - Using approximate mode without verifying sort requirements
- Referencing whole columns on very large sheets unnecessarily
- Assuming availability on older Excel versions
FAQ
Does XLOOKUP require sorted data?
Exact match mode does not require sorting. Approximate and binary search modes have their own sorting rules—read the match_mode argument.
Can XLOOKUP return values from the left?
Yes. The return_array can be anywhere relative to lookup_array.
What does if_not_found do?
It replaces #N/A with a value or text you specify, similar to wrapping VLOOKUP in IFERROR.
Is XLOOKUP available in every Excel?
It requires a supported Excel version or Microsoft 365. Older installs may need VLOOKUP or INDEX/MATCH.
Can XLOOKUP return multiple columns?
Yes, when return_array spans multiple columns, XLOOKUP can spill the matching row in modern Excel.
Comparison
| Function | Era | Flexibility |
|---|---|---|
| XLOOKUP | Modern | High |
| VLOOKUP | Classic | Lookup column must be leftmost |
| INDEX/MATCH | Classic | Very flexible |
Example
=XLOOKUP("P-102", A2:A6, C2:C6, "Not found")=VLOOKUP("P-102", A2:C6, 3, FALSE)
Advanced examples
XLOOKUP + dynamic return column
Return different columns based on a dropdown by feeding a CHOOSE-built array or multiple stacked XLOOKUP patterns (keep ranges the same height).
Two-criteria feel with helper column
Build =A2&"|"&B2 keys in a helper column, then XLOOKUP a combined key—simple and auditable.