XLOOKUP
The modern replacement for VLOOKUP and HLOOKUP. Searches any array and returns a matching value from another array, in any direction.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Writing one for your own sheet? The XLOOKUP Generator builds a ready-to-paste XLOOKUP formula from your ranges and criteria.
Arguments
| Argument | What it takes |
|---|---|
lookup_value | The value to search for. |
lookup_array | The array or range to search. |
return_array | The array or range to return a value from. |
if_not_found | Value to return when no match is found (avoids #N/A). |
match_mode | 0 exact (default), -1/1 next smaller/larger, 2 wildcard. |
search_mode | 1 first-to-last (default), -1 last-to-first. |
Examples
=XLOOKUP(A2, D2:D100, F2:F100, "Not found")Returns: Matching F value, or "Not found"
No column counting, and the return array can be left or right of the lookup array.
Try XLOOKUP
Edit the formula or any cell in the table - the result recalculates as you type, right in your browser.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ||||||
| 2 | ||||||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 |
How XLOOKUP evaluates
XLOOKUP takes a lookup_array and a return_array as two separate arguments rather than one block plus a column number. Because the two arrays are independent, the return column can sit anywhere - left of the search column, on another sheet, or as a whole block of columns returned at once. Nothing has to be counted, and inserting a column between the two arrays does not break the formula.
Its defaults are the opposite of VLOOKUP's, and that is the main reason to prefer it. match_mode defaults to 0, exact match, so an unsorted list is safe out of the box. search_mode defaults to 1, first to last; pass -1 to search bottom-up, which is how you get the most recent row of a transaction log. The optional fourth argument, if_not_found, replaces the whole IFERROR wrapper - and unlike IFERROR it catches only the miss, leaving genuine errors in the data visible.
The cost is availability. XLOOKUP arrived with Microsoft 365 and Excel 2021; it does not exist in 2019 or earlier and a workbook using it opens in those versions showing #NAME? with the function name preserved as _xlfn.XLOOKUP. If the file will be opened by anyone on a perpetual license older than 2021, INDEX/MATCH is the portable form of the same idea.
- Arrays must be the same length
- lookup_array and return_array are matched position by position. If one is 100 rows and the other 99, Excel returns #VALUE! rather than guessing an alignment. return_array may be wider than one column; the whole matching row comes back.
- Returning a block spills
- When return_array spans several columns, XLOOKUP returns the entire matching row as a dynamic array and it spills into the cells to the right. Those cells must be empty or you get #SPILL!.
- Exact by default, approximate on request
- match_mode 0 is exact. -1 returns the next smaller item when there is no exact hit, 1 the next larger, and 2 enables * and ? wildcards. Only match modes -1 and 1 have any interest in sort order, and even then XLOOKUP does not require the array to be sorted unless search_mode is set to a binary mode.
- Binary search modes assume a sort
- search_mode 2 and -2 run a binary search over data you promise is sorted ascending or descending. They are fast on very large arrays and return nonsense on unsorted data, with no warning.
- if_not_found only catches the miss
- The fourth argument fires when nothing matched. If the lookup_array itself contains an error, or the lookup value is an error, that error propagates instead - which is the behavior you want, and what makes if_not_found safer than wrapping the call in IFERROR.
- Blank cells match empty text
- An empty cell in lookup_array is treated as matching an empty lookup value, so a blank key can find a blank row. Guard with an IF on the lookup value if blanks should return nothing.
Errors XLOOKUP returns
| Error | What it means and how to fix it |
|---|---|
#N/A | Nothing in lookup_array matched and no if_not_found argument was supplied. Add a fourth argument, or check the value for the usual invisible mismatches before assuming it is genuinely absent. |
#VALUE! | lookup_array and return_array are different sizes, or match_mode / search_mode is outside its permitted set (-1, 0, 1, 2 for match; -2, -1, 1, 2 for search). Make both arrays cover the same rows. |
#NAME? | The Excel version predates XLOOKUP. In a file created in 365 and opened in 2016 or 2019 the formula appears as _xlfn.XLOOKUP. Rewrite it as INDEX/MATCH; there is no compatibility mode that makes it evaluate. |
#SPILL! | The result is a multi-column or multi-row array and something already occupies the cells it needs. Clear the obstruction or point the formula at a single-column return_array. |
#REF! | One of the arrays refers to a deleted range, or the lookup is aimed at a closed workbook. Repoint the argument at a live range. |
More examples
=XLOOKUP(A2, Log[Ref], Log[Status], "Open", 0, -1)Returns: The most recent status for the reference in A2
search_mode -1 walks the log from the bottom, so the newest row wins when a reference appears several times. if_not_found supplies a default instead of #N/A.
=XLOOKUP(A2, $C$2:$C$500, $D$2:$G$500, "")Returns: Four columns of the matching row, spilled across
A multi-column return_array returns the whole matching row at once, so one formula fills D through G. The cells to the right must be empty.
=XLOOKUP(B2, $F$2:$F$12, $G$2:$G$12, , -1)Returns: The commission rate for the band B2 falls into
match_mode -1 returns the next smaller band floor - the XLOOKUP equivalent of a sorted approximate VLOOKUP, but without needing the key column to be leftmost.
Compatibility
| Application | Support |
|---|---|
| Excel 2007-2016 | Not available. Formulas containing it show #NAME? as _xlfn.XLOOKUP - use INDEX/MATCH. |
| Excel 2019 | Not available. This is the most common reason a shared workbook breaks: 2019 is still supported but never received XLOOKUP. |
| Excel 2021 / Microsoft 365 | Fully supported, including search_mode -1 and the binary modes. Excel for the web has it too. |
| Google Sheets | Supported since August 2022, with the same six arguments. Older Sheets files written before then use INDEX/MATCH. |
| LibreOffice Calc | Added in version 24.8. Earlier releases, including the 7.x line still shipped by many Linux distributions, do not have it. |
Does XLOOKUP Work in Google Sheets?
Yes. XLOOKUP uses the same syntax in Google Sheets and LibreOffice Calc, so you can paste these formulas straight across.
In the ExcelTool editor
The ExcelTool editor accepts XLOOKUP with three to five arguments: lookup_value, lookup_array, return_array, if_not_found and match_mode. The sixth argument, search_mode, is not supported, so searches always run first to last.
match_mode 0 and an omitted match_mode are exact; -1 falls back to the largest key at or below the lookup value and 1 to the first key at or above it, each only after an exact scan has failed. Text comparison is case-insensitive, and a lookup value containing * or ? is always treated as a wildcard pattern rather than only under match_mode 2.
Orientation is inferred from the shape of lookup_array: taller than it is wide means a column search, otherwise a row search. When return_array is wider than the match, the whole matching row is returned as an array and spills into the neighbouring cells, or reports #SPILL! if they are occupied.
Omitting if_not_found gives #N/A, as in Excel. Unlike Excel, the editor does not reject a return_array that is a different length from lookup_array; a position past the end of the return array comes back as #N/A instead of #VALUE!.
You can try any of these formulas in the free in-browser spreadsheet editor - it opens .xlsx files, recalculates as you type, and needs no signup.
Build a XLOOKUP formula
Fill in your ranges and copy a ready-to-paste formula - no signup.
XLOOKUP questions
They are on Excel 2019 or older. XLOOKUP shipped with Microsoft 365 and Excel 2021, and older versions have no way to evaluate it - when the file opens they see _xlfn.XLOOKUP and #NAME?. Saving in a different format does not help, because the function simply does not exist in that build. Replace it with =INDEX(returnRange, MATCH(key, lookupRange, 0)), which behaves the same and works back to Excel 2003.
Set the sixth argument, search_mode, to -1. That searches from the end of the array towards the start and returns the first match it meets, which is the last one in sheet order. It is the standard way to get the current value from an append-only log. Do not confuse it with search_mode -2, which is a binary search over descending data and requires the array to be sorted.
Yes, and for a specific reason. IFERROR replaces every error, so a #REF! or a corrupted number in the return column disappears behind the same friendly text as a genuine miss. if_not_found fires only when the search found nothing; any other error still surfaces. If you are on a version without XLOOKUP, IFNA is the closer equivalent to it than IFERROR.
Yes, with no change of function - hand it a horizontal lookup_array and a horizontal return_array of the same width and it works across rather than down. That single behavior replaces HLOOKUP entirely, and unlike HLOOKUP the return row can be above the search row.