Excel
XLOOKUP Function in Excel
XLOOKUP searches a range or array and returns the corresponding value from a second range. It replaces VLOOKUP and HLOOKUP with a simpler, more flexible syntax.
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Parameters
lookup_valueRequiredThe value to search for.
lookup_arrayRequiredThe range or array to search in.
return_arrayRequiredThe range or array to return a value from.
if_not_foundOptionalValue returned when no match is found.
match_modeOptional0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard.
search_modeOptional1 first to last (default), -1 last to first, 2 binary asc, -2 binary desc.
Examples
Basic lookup
=XLOOKUP("Widget", A2:A100, B2:B100)Finds "Widget" in column A and returns the matching value from column B.
With fallback
=XLOOKUP(E2, A2:A100, B2:B100, "Not found")Returns "Not found" instead of #N/A when the lookup fails.
Common Errors
- #N/A
Cause: No exact match was found and no if_not_found value was provided.
Fix: Add a fourth argument, e.g. "Not found" or 0, to handle missing values gracefully.
- #VALUE!
Cause: lookup_array and return_array have different sizes.
Fix: Ensure both arrays span the same number of rows (or columns).
Best Practices
- Prefer XLOOKUP over VLOOKUP for new workbooks — it is faster and safer.
- Always set an if_not_found argument to avoid #N/A leaking into dashboards.
- Use structured table references (Table[Column]) for self-expanding ranges.
Frequently Asked Questions
Need a custom version of this formula?
Describe what you need in plain English and get a ready-to-use formula in seconds.
Generate this formula with XLFormula AI