Lookup and reference
XLOOKUP function playground
Finds a value in one range and returns the matching item from another, in any direction.
The playground runs in your browser. Nothing you type is sent anywhere.
How XLOOKUP works
XLOOKUP searches lookup_array for the value and returns the item in the same position of return_array. Because the two ranges are separate, the return column can be to the left.
It matches exactly by default, has a built-in "not found" value, and can return several columns at once: they spill into the cells to the right.
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])| Argument | What it means |
|---|---|
lookup_valuerequired | The value to find. |
lookup_arrayrequired | The single column or row to search. |
return_arrayrequired | The column(s) or row(s) to return from. Same height as lookup_array. |
if_not_foundoptional | What to show when there is no match. Without it you get #N/A. |
match_modeoptional | 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard. |
search_modeoptional | 1 first to last (default), -1 last to first. |
Examples
| Formula | What it does | Result |
|---|---|---|
=XLOOKUP(I1,A2:A9,B2:B9) | Name for the ID in I1. | Jonas Berg |
=XLOOKUP("Lily Chen",B2:B9,A2:A9) | Look to the left: find the ID from a name. | E-103 |
=XLOOKUP(I1,A2:A9,C2:D9) | Return two columns at once (department and salary) - they spill. | Sales (spills 1×2) |
=XLOOKUP("Sales",C2:C9,B2:B9,,0,-1) | Search from the bottom: the last Sales employee listed. | Tom Walsh |
=XLOOKUP("E-120",A2:A9,B2:B9,"Not found") | A friendly message when the ID is missing. | Not found |
Common mistakes
Ranges of different sizeslookup_array and return_array must have the same number of rows, or you get #VALUE!.
Older ExcelExcel 2019 and earlier don't have XLOOKUP and show #NAME?.
Related functions
Learn more
Download the example workbook (.xlsx)
The workbook has the same sample data and formulas, plus a practice sheet.