XLOOKUP in Excel: Everything a Beginner Needs

XLOOKUP is Excel's modern lookup: you tell it what to find, where to look and what to bring back, and it returns the match. The XLOOKUP Excel function defaults to an exact match, can look left or right, and lets you choose what to show when nothing is found. It needs Excel 2021, Excel 2024 or Microsoft 365. This guide is for beginners and for VLOOKUP users who want to switch.

What is XLOOKUP and which Excel versions have it?#

XLOOKUP is a lookup function that searches one range and returns the item in the same position from another range. It replaces VLOOKUP, HLOOKUP and most INDEX MATCH formulas with one function.

It is available in Microsoft 365, Excel 2021, Excel 2024 and Excel for the web, on Windows and Mac. It is not in Excel 2019, 2016 or earlier. If you open a file that uses XLOOKUP in one of those versions, the cells show #NAME?. So before you use it in a shared file, check what version your team uses. For a full side-by-side, read VLOOKUP vs XLOOKUP: which should you use?

XLOOKUP Excel syntax: the six arguments#

Only the first three arguments are required:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

The lookup_array and return_array must be the same size. There is no column number to count, which is why XLOOKUP keeps working when someone inserts a column. The XLOOKUP function guide has a short reference.

How to use XLOOKUP: a basic example#

Here is a cafe's stock list. The SKU is in column B, and the product name is to its left, in column A:

With a SKU in G2, this returns the product name:

=XLOOKUP(G2,B2:B9,A2:A9)

If G2 is SY-045, XLOOKUP finds it in the 4th row of B2:B9 and returns the 4th item of A2:A9: Vanilla Syrup. VLOOKUP can't do this, because it only returns columns to the right. See more ways to look up to the left in Excel.

To return a different column, change only the return_array: =XLOOKUP(G2,B2:B9,C2:C9) returns the category. Before you fill the formula down, lock both ranges with $ signs: =XLOOKUP(G2,$B$2:$B$9,$A$2:$A$9).

XLOOKUP returns a real value, so you can use it inside other formulas. This shows "Yes" when a SKU's stock is below 100:

=IF(XLOOKUP(H12,B2:B9,D2:D9)<100,"Yes","No")

For DR-220 (Oat Milk, 85 in stock) the result is Yes.

How to show a message instead of #N/A#

Put the message in the 4th argument, if_not_found. A SKU like PK-399 isn't in the list, so a plain XLOOKUP returns #N/A. This version shows a clear message instead:

=XLOOKUP(G2,$B$2:$B$9,$A$2:$A$9,"Not found")

You don't need to wrap XLOOKUP in IFERROR or IFNA. That is better, too: IFERROR would also hide real mistakes, like a misspelled function. Our guide on how to fix #N/A errors explains why a value might be "missing" when it looks like it's there, such as extra spaces.

Match modes and search modes#

The 5th and 6th arguments change how XLOOKUP matches and in which direction it searches.

Match mode (5th argument):

• 0: exact match (the default).
• -1: exact match, or the next smaller value. Use it for bands, like a rate that starts at 5 kg. =XLOOKUP(H2,$D$2:$D$6,$E$2:$E$6,,-1) returns the rate for a 7.5 kg parcel from the 5 kg band.
• 1: exact match, or the next larger value. Use it to find the smallest box that fits.
• 2: wildcard match, so "Riverside*" finds a name that starts with Riverside.

The two commas in ,,-1 skip the 4th argument. Unlike VLOOKUP with TRUE, modes -1 and 1 don't need a sorted list. Wildcards have their own guide: XLOOKUP wildcards and match modes explained.

Search mode (6th argument): -1 searches from the bottom up, so it returns the last match. With a list of orders in date order, =XLOOKUP(G2,B2:B50,D2:D50,,0,-1) returns the latest order for a customer.

Return several columns at once#

Give XLOOKUP a return_array that is more than one column wide, and the results spill into the cells next to it:

=XLOOKUP(G2,B2:B9,C2:D9)

This returns the Category and In Stock for the SKU in G2, in two cells side by side. The cells to the right must be empty, or you get a #SPILL! error.

XLOOKUP vs VLOOKUP on the same task#

To return a SKU's category, both functions work, but they ask different questions:

=VLOOKUP(G2,$B$2:$D$9,2,FALSE)
=XLOOKUP(G2,$B$2:$B$9,$C$2:$C$9)

VLOOKUP needs a table that starts at the SKU column, a column number you count by hand (Category is the 2nd column of B:D), and FALSE for an exact match. XLOOKUP needs only the column to search and the column to return. If someone inserts a column between SKU and Category, the VLOOKUP's 2 now points at the new column, while the XLOOKUP keeps returning the category. For a full comparison, see VLOOKUP vs XLOOKUP.

Common XLOOKUP mistakes#

• Ranges of different sizes. =XLOOKUP(G2,B2:B9,A2:A8) returns #VALUE!. Make both ranges cover the same rows.
• Swapping the ranges. The search range comes first, then the return range. Swapped, you search the names for a SKU and get #N/A.
• Putting the match mode in the 4th place. =XLOOKUP(H2,D2:D6,E2:E6,-1) treats -1 as the not-found value, so a 7.5 kg parcel gets -1 instead of a rate. Use two commas to skip to the 5th argument.
• Sharing with older Excel. A coworker on Excel 2019 sees #NAME?. Use VLOOKUP or INDEX MATCH for those files.

Lookups are a core skill in analyst work. See which others matter in our guide to Excel skills for data analysts.

Practise it#

XLOOKUP is easy to read but easy to mistype under time pressure, so practise it until the order of the ranges is automatic. ExcelReady's XLOOKUP lesson has you look up to the left, add a "Not found" message, return other columns and use XLOOKUP inside IF, in a live spreadsheet. Then move on to XLOOKUP wildcards and match modes, where you search partial names and place parcels into weight bands.