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])
ArgumentMeaning
lookup_valueThe value to find.
lookup_arrayThe single column (or row) to search.
return_arrayThe column (or columns) to return from.
if_not_foundOptional text or value to show when nothing matches.
match_mode0 = exact (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard.
search_mode1 = 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

VLOOKUPXLOOKUP
Default matchApproximateExact
Look leftNoYes
Survives inserted columnsNoYes
Not-found messageNeeds IFERRORBuilt in
Search from the bottomNoYes
Works in Excel 2016/2019YesNo
Sharing files: if colleagues use Excel 2019 or earlier, XLOOKUP formulas show #NAME? on their computers. Use VLOOKUP or INDEX/MATCH for those files.

Next: make decisions in your formulas with the IF function.