How to Use XLOOKUP in Excel

XLOOKUP is the lookup function most people should learn first. You tell it what to find, where to look and what to bring back, and it does the rest. It's simpler than VLOOKUP, it can look in any direction, and it uses an exact match unless you ask for something else.

All the examples below use this small staff list, in cells A1:D9:

#The XLOOKUP syntax

XLOOKUP has three required arguments and three optional ones. Most of the time you only need the first three.

The one rule to remember: lookup_array and return_array must be the same size. If you search B2:B9, return from C2:C9, not C2:C10.

#A basic XLOOKUP example

Say you want to know which department Grace Kim works in. Search the names in column B, and return the matching value from column C:

Read it out loud and it makes sense: find "Grace Kim" in B2:B9, and give me the value from C2:C9 in the same row. Excel finds the name in the third row of the range, so it returns the third value of C2:C9.

In a real workbook you'd usually point at a cell instead of typing the name, so the result updates when the name changes. If the name is in G2:

Now try one yourself. The sheet below is live: click cells to add them to your formula, then press Enter.

#Look up a value to the left

VLOOKUP can only return columns to the right of the column it searches. XLOOKUP doesn't care about direction, because the lookup and return ranges are separate. To find the ID of a person by name, search column B and return column A:

#Return several columns at once

If return_array is more than one column wide, XLOOKUP returns the whole row of results and spills it into the cells to the right:

This is handy for building a small lookup panel: type an ID in one cell and see the name, department and salary next to it. The cells the result spills into must be empty, or you'll see a #SPILL! error.

#When the value isn't there

If XLOOKUP can't find a match, it returns #N/A. That's correct, but it looks like something broke. Use the fourth argument to show a friendlier message:

This is better than wrapping the whole formula in IFERROR, because IFERROR also hides real mistakes, like a typo in a range.

#Approximate matches with match_mode

Some lookups don't have an exact answer. Commission tiers, tax brackets and grade boundaries all work by finding the band a number falls into. Say this tier table is in A2:B5 on another sheet:

To find the rate for $31,500 in sales, ask for an exact match or the next smaller value with match_mode -1:

The empty space between the two commas skips the if_not_found argument. Unlike VLOOKUP's approximate match, XLOOKUP doesn't need the table to be sorted for this to work.

match_mode 2 turns on wildcards, where * means "any characters". This finds the first name starting with "Sam":

#Search from the bottom with search_mode

By default XLOOKUP returns the first match from the top. If a value appears more than once and you want the last one, set search_mode to -1. Finance appears twice in the list:

This is useful for things like "the most recent order for this customer" in a list sorted by date.

#Nesting XLOOKUPs

Because XLOOKUP returns a value, you can feed that value into another XLOOKUP. For example, the inner XLOOKUP can find a person's department, and the outer one can look that department up in a second table to find its bonus rate. You can practice exactly that in the exercises at the end of this guide.

#XLOOKUP vs VLOOKUP

If everyone who opens your file has Excel 365 or Excel 2021 or later, use XLOOKUP. Here's why:

• Exact match by default. VLOOKUP defaults to an approximate match, which quietly returns wrong answers if you forget FALSE.
• It looks left. VLOOKUP can only return columns to the right of the search column.
• Inserting columns doesn't break it. VLOOKUP uses a column number like 3, which points at the wrong column after you insert one.
• Built-in "not found" message and search from the bottom, with no extra functions.

VLOOKUP still matters, though. Older files are full of it, and many Excel job tests ask for it. Our VLOOKUP guide covers it step by step.

#Common XLOOKUP errors and how to fix them

#Which versions of Excel have XLOOKUP?

XLOOKUP works in Microsoft 365, Excel 2021, Excel 2024 and Excel for the web. It isn't available in Excel 2019 or older. Google Sheets supports XLOOKUP too, with the same arguments.

#Key takeaways

• XLOOKUP needs three things: what to find, where to look and what to return.
• The lookup and return ranges must be the same size.
• Use the fourth argument for a friendly "not found" message.
• Use match_mode -1 or 1 for bands and tiers, and search_mode -1 to get the last match.