Why you need this
XLOOKUP is the modern, superior replacement for VLOOKUP. It defaults to exact match, handles arrays, and doesn't care about column positions.
One-Click Copy
=XLOOKUP(lookup_value, lookup_array, return_array)
Copy
Quick context
- • Works in both Excel and Google Sheets.
- • Use when you need consistent, auditable results.
- • Copy the snippet above and adjust only the ranges.
When to use
Learn how to use the Excel XLOOKUP Formula formula.
Disclaimer: While we strive for accuracy, these formulas are provided "as is" without warranty of any kind. Please verify all results before use.
Common Mistakes
- ✕ Using wrong match mode. The default is Exact Match (0), but if you need Wildcard matches (*), you must explicitly set the expected argument.
- ✕ Forgetting 'if_not_found'. XLOOKUP allows you to define a default value if no match is found. Skipping this often leads to ugly #N/A errors.
Best Practices
- ✓ Return entire rows or columns. You can return multiple values at once (Spill Array) by selecting multiple columns as the return array.
- ✓ Use wildcard match. Set the match_mode to 2 to use wildcards like '*' or '?' for powerful partial text searches.