XLOOKUP in Excel: The Modern Way to Look Up Data
Last updated: November 2026 · Works in Excel for Microsoft 365, Excel 2021 and later
XLOOKUP replaces VLOOKUP, HLOOKUP and most INDEX/MATCH formulas with one function that is easier to read and harder to break. It can look left or right, returns an exact match by default, handles "not found" without IFERROR, and can return several columns at once. It is available in Microsoft 365, Excel 2021 and later, and Excel for the web.
XLOOKUP syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| Argument | Meaning |
|---|---|
| lookup_value | The value to find. |
| lookup_array | The single column (or row) to search. |
| return_array | The column (or columns) to return from. |
| if_not_found | Optional text or value to show when nothing matches. |
| match_mode | 0 = exact (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard. |
| search_mode | 1 = first to last (default), -1 = last to first. |
Basic example: find an employee's department
Employee IDs are in column A of the Staff sheet and departments in column D. With an ID in B2:
=XLOOKUP(B2, Staff!A:A, Staff!D:D)
Compare that with the VLOOKUP version, =VLOOKUP(B2,Staff!A:D,4,FALSE). XLOOKUP needs no column number and no FALSE, and it keeps working if someone inserts a new column in the Staff sheet.
Looking to the left
Because the lookup and return ranges are separate, the return column can be anywhere. If names are in column B and IDs in column E, you can still return the name from an ID:
=XLOOKUP(H2, E:E, B:B)
Built-in "not found" message
=XLOOKUP(B2, Staff!A:A, Staff!D:D, "ID not found")
Only a genuine "no match" shows the message; other errors still appear, which helps you spot real problems.
Return several columns at once
If the return array is several columns wide, XLOOKUP "spills" the whole row of results into neighbouring cells:
=XLOOKUP(B2, Staff!A:A, Staff!B:D)
This returns the name, job title and department in three cells from one formula. Make sure the cells to the right are empty, or you will see a #SPILL! error.
Bands and tiers without sorting
For a discount table with order-value thresholds in F2:F5 and rates in G2:G5:
=XLOOKUP(B2, F2:F5, G2:G5, 0, -1)
Match mode -1 returns the exact value or the next smaller one, so an order of 7,800 picks the rate for the 5,000 threshold. Unlike VLOOKUP's approximate match, the threshold list does not need to be sorted.
Find the most recent entry
To get the latest price paid for a product in a purchase log listed by date, search from the bottom up:
=XLOOKUP(B2, Log!C:C, Log!E:E, "None", 0, -1)
Two-way lookup
To find a value at the crossing of a row and a column, such as sales for a branch (in J2) and a month (in J3) in a grid where branches are in A2:A10, months are in B1:M1 and figures are in B2:M10, nest one XLOOKUP inside another:
=XLOOKUP(J2, A2:A10, XLOOKUP(J3, B1:M1, B2:M10))
The inner XLOOKUP returns the whole column for the chosen month, and the outer one picks the branch's row from it.
XLOOKUP vs VLOOKUP at a glance
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Default match | Approximate | Exact |
| Look left | No | Yes |
| Survives inserted columns | No | Yes |
| Not-found message | Needs IFERROR | Built in |
| Search from the bottom | No | Yes |
| Works in Excel 2016/2019 | Yes | No |
#NAME? on their computers. Use VLOOKUP or INDEX/MATCH for those files.Next: make decisions in your formulas with the IF function.