XLOOKUP
XLOOKUP is the lookup function with nothing to remember, which makes availability the only real question on this hub. Exact matching is already the default, the two ranges are independent so direction stops mattering, and the fourth argument holds a not-found message instead of another matching flag. None of that helps if the file travels. XLOOKUP arrived with Microsoft 365 and Excel 2021 and was never backported, so every cell using it reads #NAME? for anyone still on 2019 — settle that before the workbook is shared, because INDEX MATCH is the build that survives the trip.
The formula
=XLOOKUP(F2, A2:A500, C2:C500) exact match by default; no fourth argument needed
=XLOOKUP(F2, A2:A500, C2:C500, "not found") the fourth argument replaces IFERROR
=XLOOKUP(F2, A2:A500, B2:D500) returns three columns at once, spilling to the right
=XLOOKUP(F2&G2, A2:A500&B2:B500, C2:C500) two criteria without a helper column
=XLOOKUP(F2, A1:H1, A9:H9) a horizontal lookup — same function, HLOOKUP not required
=XLOOKUP(F2, A2:A500, C2:C500, , 0, -1) searches bottom-up, so the most recent matching row wins
=XLOOKUP(F2, A2:A500, C2:C500, , -1) nearest smaller value: banded rates without sorting the tableA worked example
A2:A500 holds employee IDs, B2:D500 holds each employee's name, department and start date. F2 holds the ID being looked up.
=XLOOKUP(F2, A2:A500, B2:D500, "no such ID")
Three cells fill at once — name, department and start date spilling across from the formula cell — or the single text value "no such ID" if F2 is not in column A. The same result from VLOOKUP takes three separate formulas with three different column numbers, each needing its own IFERROR, and all three break the day a column is inserted into the table.
Which one do I need?
| If you want to… | Use |
|---|---|
| Any ordinary lookup on Excel 365 or 2021 | =XLOOKUP(key, key_range, return_range) — exact match already, nothing else to add |
| The answer sits left of the key column | No special handling at all; the two ranges are independent arguments |
| #N/A should read as something friendlier | Fourth argument: =XLOOKUP(key, keys, values, "not found") |
| Several columns should come back from one lookup | Give return_array more than one column and the result spills across |
| Two values together identify the row | Join both sides: =XLOOKUP(A2&B2, keys1&keys2, values) |
| Several rows match and you want the most recent | Sixth argument -1 searches from the bottom up instead of the top down |
| A banded lookup — the rate for a value between thresholds | Fifth argument -1 takes the next smaller value, 1 the next larger, with no sorting requirement |
| Someone might open the file in Excel 2019 or earlier | They will see #NAME? — build it with INDEX/MATCH instead |
| Every matching row is wanted, not one | XLOOKUP returns a single match; FILTER returns them all |
Frequently asked questions
Which versions of Excel have XLOOKUP?
Excel for Microsoft 365 and Excel 2021 and later, on Windows, Mac and the web. Excel 2019, 2016 and earlier do not have it and never will — the function is not backported. A file authored with XLOOKUP opens in those versions with #NAME? in every cell that uses it.
XLOOKUP versus VLOOKUP — what actually changes?
Exact match becomes the default instead of something you must remember to request, the lookup and return ranges become independent so direction stops mattering, a built-in if_not_found argument removes the IFERROR wrapper, several columns can be returned in one formula, and there is no column number to be invalidated by an inserted column. VLOOKUP's remaining advantage is that it runs everywhere.
Why does XLOOKUP show #NAME?
Excel does not recognise the function name. Either the workbook is open in a version older than 2021, or the name is mistyped, or the file arrived from someone on Microsoft 365 while you are on a perpetual licence. Checking File > Account settles it in a few seconds.
Can XLOOKUP return several columns or a whole row?
Yes — pass a multi-column range as return_array and the matching slice spills out from the formula cell. That requires the cells to its right to be empty; anything in the way produces #SPILL! instead. The same works vertically when the table is laid out sideways.
New guides and tools, once a month
DE + EN · double opt-in · no spam