XLOOKUP Function: syntax, examples, mistakes and translation notes
Looks up a value in a range and returns a corresponding result.
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Arguments
lookup_value
Value to find.
lookup_array
One-dimensional range to search.
return_array
Range that supplies the result.
if_not_found
Optional fallback when no match exists.
match/search mode
Optional controls for match type and search direction.
Worked examples
=XLOOKUP(E2,A:A,C:C,"Not found")
Finds E2 in column A and returns the same-row value from C.
=XLOOKUP(E2,A:A,C:C,"",0,-1)
Uses exact match and searches from the last item upward.
Practical tips
- Keep lookup and return arrays the same height or width.
- Use the built-in if_not_found argument instead of wrapping every lookup in IFERROR.
- Explicit match/search modes make intent clearer in complex workbooks.
Common mistakes
- Using arrays with different shapes.
- Assuming text IDs and numeric IDs match automatically when their types differ.
Related functions
Frequently asked questions
What does XLOOKUP do?
Looks up a value in a range and returns a corresponding result.
Why might a XLOOKUP formula return an unexpected result?
Using arrays with different shapes. Start by testing the smallest referenced range or condition independently before adding wrappers or fallbacks.
Check the complete formula
Function syntax is only one part of a working workbook. Paste the finished expression into the Formula Checker for structural warnings, the Formatter for readability or the Translator when a supported locale mapping is involved.
Testing note: examples are educational. Spreadsheet versions, locale settings, tables, named ranges and workbook data can change behavior, so validate important formulas with representative data.