XLOOKUP
Lookup FormulasMaster XLOOKUP, Excel's modern lookup function. Learn syntax, examples, and why XLOOKUP replaces VLOOKUP for more flexible data retrieval.
Syntax
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Parameters
lookup_valueThe value to search for
lookup_arrayThe array or range to search
return_arrayThe array or range to return
if_not_foundValue to return if no match found (optional)
match_mode0=exact, -1=exact or smaller, 1=exact or larger, 2=wildcard (optional)
search_mode1=first to last, -1=last to first, 2=binary ascending, -2=binary descending (optional)
Examples
=XLOOKUP(A2, B:B, C:C)Simple lookup returning value from column C
=XLOOKUP(A2, B:B, C:C, "Not Found")Lookup with custom error message
Common Errors
#N/A - Value not found (use if_not_found parameter)
#VALUE! - Arrays have different sizes
Pro Tips
XLOOKUP can search left or right
Supports wildcards with match_mode 2
Returns #N/A by default if not found - use if_not_found parameter
Related Functions
Need Help with XLOOKUP?
Describe your specific use case and our AI will generate the perfect XLOOKUP formula for you.
Generate XLOOKUP Formula