XLOOKUP Generator
Build modern Excel XLOOKUP formulas with clear lookup, return, not-found, and match mode inputs.
XLOOKUP
Build a modern lookup formula that can search left, right, up, or down.
Cell or value to find.
One-column or one-row range to search.
Range with values to return.
Value to display when no match exists.
0 means exact match.
=XLOOKUP(A2, $D$2:$D$100, $E$2:$E$100, "Not found", 0)Checks Before You Paste
- •XLOOKUP is available in Microsoft 365 and Excel 2021+.
- •The lookup and return arrays must be the same length.
- •Use match mode 0 for exact matches.
How XLOOKUP works
Availability: Microsoft 365 and Excel 2021 or later, on Windows, Mac and the web. Google Sheets added XLOOKUP in 2022. Excel 2019 and earlier do not have it and show #NAME? where the formula sits.
XLOOKUP takes a lookup array and a return array as two separate arguments rather than a single table plus a column number. That one change removes most of VLOOKUP's failure modes: the return array can sit anywhere, including to the left of the lookup array or on another sheet, and inserting a column between them moves both references rather than silently repointing an index number.
The two arrays must be the same length and orientation. Give it a 99-row lookup array and a 100-row return array and Excel returns #VALUE! rather than guessing. The return array is allowed to be more than one column wide, in which case XLOOKUP returns the whole matching row as a spilled array - so a single formula can pull name, department and start date in one go, provided the cells to the right are empty.
The fourth argument, if_not_found, is the reason most people switch. It replaces the IFERROR wrapper that used to sit around every lookup, and it is more precise: IFERROR swallows #VALUE! and #DIV/0! raised inside the formula as well as the miss, while if_not_found only fires when the lookup value genuinely is not there. The fifth argument, match_mode, defaults to 0 for an exact match - the opposite of VLOOKUP's default, and a real improvement. Set it to -1 for the next smaller item, 1 for the next larger, or 2 to treat the lookup value as a wildcard pattern using * and ?.
The sixth argument, search_mode, controls direction. 1 searches first to last, -1 searches last to first and so returns the most recent entry in an append-only log, and 2 or -2 run a binary search that is faster on very large sorted arrays but returns nonsense if the data is not actually sorted. Unlike VLOOKUP's approximate match, modes -1 and 1 do not require sorted data at all.
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])| Argument | Required | What it does |
|---|---|---|
lookup_value | Required | The value to search for. With match_mode 2 it can contain the wildcards * and ?. |
lookup_array | Required | The single row or column to search. It does not have to be the first column of anything, and it does not have to be sorted. |
return_array | Required | The row or column to take the answer from, the same length as lookup_array. Give it several columns and the match spills across them. |
if_not_found | Optional | What to return when nothing matches. Omit it and a miss returns #N/A. "" gives a blank cell; 0 gives a zero you can add up. |
match_mode | Optional | 0 (default) exact; -1 exact or the next smaller item; 1 exact or the next larger item; 2 wildcard match on * ? and ~. |
search_mode | Optional | 1 (default) searches top to bottom; -1 searches bottom to top and returns the last match; 2 and -2 use a binary search and require sorted data. |
More worked examples
An employee list has names in C2:C500 and staff numbers in A2:A500 - the ID sits to the left of the name, so VLOOKUP cannot reach it. F2 holds the name being looked up.
=XLOOKUP(F2, C2:C500, A2:A500, "No such employee")Returns: The staff number for that name, or the text No such employee when the name is not on the list.
Neither array has to be first or last; XLOOKUP simply pairs them row for row.
A price history log appends a new row each time a price changes, with product codes in A2:A2000 and prices in C2:C2000. You want the current price, meaning the last row for that code.
=XLOOKUP(E2, A2:A2000, C2:C2000, , 0, -1)Returns: The price from the most recent row for that product, not the first one recorded.
search_mode -1 walks the array bottom to top. Note the empty slot for if_not_found - leave it out entirely and Excel reads 0 as if_not_found rather than as match_mode.
Invoice references in B2:B300 look like INV-2026-0043 and you only know the year and sequence. Amounts sit in D2:D300.
=XLOOKUP("*2026-0043", B2:B300, D2:D300, "Not billed", 2)Returns: The amount on the invoice whose reference ends in 2026-0043.
match_mode 2 turns on wildcards: * matches any run of characters, ? matches one, and ~ escapes a literal asterisk or question mark.
Common mistakes
- Arrays of different lengths
- lookup_array and return_array must have the same number of cells. C2:C500 paired with A2:A501 returns #VALUE!, and it is an easy mismatch to create by extending one range and not the other. Convert the source to an Excel Table and reference columns by name so both grow together.
- Passing match_mode into the if_not_found slot
- =XLOOKUP(A2,D:D,E:E,0) does not request an exact match - it says "return 0 when nothing is found". The optional arguments are positional, so you have to keep the comma: =XLOOKUP(A2,D:D,E:E,,0).
- A spilled result blocked by an occupied cell
- When return_array is more than one column wide the answer spills sideways. If anything at all sits in the cells it needs, the formula returns #SPILL! instead. Clear the block to its right, or narrow return_array to the single column you actually want.
- Binary search on unsorted data
- search_mode 2 and -2 assume the lookup array is sorted ascending or descending. Run one over unsorted data and XLOOKUP returns a wrong value or #N/A with no warning - the same trap as VLOOKUP's TRUE. Use the default 1 unless the array really is sorted and large enough for the speed to matter.
- Saving an XLOOKUP for a colleague on Excel 2019
- Excel stores functions added after 2007 with an _xlfn prefix. Open the file in Excel 2019 or earlier and the cell reads _xlfn.XLOOKUP and shows #NAME?, even though the formula is correct. If the workbook has to travel, build it with INDEX/MATCH instead.
Frequently Asked Questions
Functionally very little - INDEX/MATCH does left-lookups and last-match too. XLOOKUP is one function instead of two, it has a built-in if_not_found so the miss case does not need an IFERROR wrapper, it can return a whole row as an array, and its exact-match default means an omitted argument cannot quietly turn into an approximate search.
Yes. Make return_array several columns wide - =XLOOKUP(A2,D2:D500,E2:G500) - and the matching row spills across three cells. Only the top-left cell holds the formula; the rest are the spill and cannot be edited individually.
Set if_not_found to "": =XLOOKUP(A2,D2:D500,E2:E500,""). Be aware that this is an empty text string, not a truly empty cell, so ISBLANK on the result is FALSE and SUM treats it as text. Use 0 if the result feeds arithmetic.
Both find the largest value at or below the lookup value, but XLOOKUP's -1 does not require the array to be sorted - it scans it. VLOOKUP's TRUE runs a binary search that assumes sorted data and returns garbage when that assumption is wrong.
The file was written by a build that has XLOOKUP and opened in one that does not - Excel 2019, 2016 or LibreOffice's older releases. The prefix is how Excel records a function it cannot resolve. There is no fix in the older version; the formula has to be rewritten as INDEX/MATCH.