How to Use XLOOKUP in Excel

You've got a product list on one tab and an orders sheet on another, and you just need the price to show up next to each order. You reach for VLOOKUP, count the columns, type the range, and it works, right up until someone inserts a column and every number shifts to the wrong row.

If you’ve gone through this, XLOOKUP is the fix. It looks up a value and returns a match without counting columns, without breaking when the sheet changes, and without the left-to-right-only rule that VLOOKUP forces on you. This guide walks you through what XLOOKUP does, its exact syntax, and real examples (single lookups, multiple criteria, and lookups across two sheets).

 

Time-saver: If you’re more of a visual learner, here’s a quick video tutorial on how to use XLOOKUP in Excel:

What is XLOOKUP (and what does it do)?

XLOOKUP is an Excel function that searches one range for a value and returns the corresponding value from another range. Think of it as "find this, give me that" - you point at what you're looking for, and where the answer is, and Excel returns it.

It replaces VLOOKUP, HLOOKUP, and most INDEX/MATCH combinations with a single function. Unlike VLOOKUP, it doesn't care whether the answer is to the left or the right of your search column, and it doesn't rely on a hard-coded column number that breaks if your layout changes.

By default, it looks for an exact match, so you don't have to remember to add FALSE at the end like you did with VLOOKUP. That one change alone removes the most common lookup error people make. Luckily, XLOOKUP is available in Microsoft 365 and newer standalone versions.

XLOOKUP syntax

The XLOOKUP formula takes three mandatory arguments and three optional ones:

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

Here's what each one means:

• lookup_value: what you're searching for (a cell like A2, or text like "SKU-114").
• lookup_array: the range you're searching in (for example, A2:A500).
• return_array: the range you want the answer from (for example, C2:C500).
• [if_not_found]: what to return when there's no match, such as "Not found". This replaces wrapping the whole thing in IFERROR. And it’s optional.
• [match_mode]: 0 is an exact match (the default), -1 or 1 find the next smaller or larger value, and 2 allows widlcards like * and ?. It’s also optional.
• [search_mode]: 1 searches first to last (default); -1 searches last to first, which is handy when you want the most recent entry. This one’s optional, too.

Most of the time you'll only use the first three.

How to use XLOOKUP in Excel

Suppose you have a product table where column A holds Product ID, column B holds Product name, and column C holds Price. You want the price for SKU-114.

1. Click the cell where you want the price to appear.
2. Type =XLOOKUP( , or go to the Formulas tab, choose Lookup & Reference, and select XLOOKUP.
3. Click the cell holding your search value (or type "SKU-114"), then type a comma.
4. Select the lookup range A2:A20 (the Product IDs), then a comma.
5. Select the return range C2:C20 (the Prices), close the bracket, and press Enter.

Your finished formula reads =XLOOKUP("SKU-114", A2:A20, C2:C20), and the matching price appears right away. Change any price in column C and the result updates on its own. If you want to find out several details at once, you need to widen the return range. Setting it to B2:C20 returns both the name and the price, spilling across two cells from one formula. VLOOKUP simply can't do that in one step.

Pro tip: If you need someone to help you in the moment, Guidy sits on top of your own screen and highlights each cell to click as you build the formula, so you follow along easily.

How to use XLOOKUP with multiple criteria

Sometimes, one lookup value isn't enough. When you need the row that meets Color and Size, XLOOKUP handles it by multiplying the two conditions together. Suppose column A is Color, column B is Size, and column C is Price. If you want to find the price of a red, large item, you can use the following:

=XLOOKUP(1, (A2:A20="Red")*(B2:B20="Large"), C2:C20)

Formula explained: each comparison returns a column of TRUE/FALSE values. Multiplying them turns each row into a 1 (both conditions true) or a 0 (at least one false). You then look up 1, and XLOOKUP returns the price from the only row that qualifies. The same pattern extends to three or four conditions: just keep multiplying the bracketed comparisons.

How to use XLOOKUP across two sheets

XLOOKUP works when your data is on a different tab. You point the lookup and return arrays at the other sheet by putting the sheet name and an exclamation mark in front of the range. Imagine your orders are on the current sheet and your catalog is saved on a tab named Products. If you want to pull a price into cell D2 based on the Product ID in A2, you can use the following:

=XLOOKUP(A2, Products!A:A, Products!C:C)

If the sheet name has a space, like Product List, Excel puts it in single quotes automatically: ='Product List'!A:A. You can select the ranges by clicking the other tab while building the formula, so you rarely have to type the reference by hand.

XLOOKUP vs VLOOKUP: which should you use?

For any new formula, XLOOKUP is the better one. It looks in both directions, defaults to an exact match, doesn’t get bothered by inserted or deleted columns, and returns multiple values at once. That's four things VLOOKUP can't claim.

The one time VLOOKUP still wins is when you want to share files with people on older Excel versions that don't have XLOOKUP. VLOOKUP will always work for everyone. We cover the full breakdown in our XLOOKUP vs VLOOKUP comparison.

When XLOOKUP won't work (Excel versions incl. Mac)

XLOOKUP isn't in every version of Excel, and this is what trips people up. It's available in Microsoft 365, Excel 2021, Excel 2024, Excel for the web, and the Mac editions of those. It is not in Excel 2019, 2016, 2013, or earlier. If you open a file that uses it there, you'll see a #NAME? error where the result should be. That's a real reason to stick with VLOOKUP or INDEX/MATCH for shared files. Being honest about this saves you a confused email later.

A possible downside of XLOOKUP is that on big datasets, such array formulas can slow down your workbook. Besides, a missing match returns #N/A unless you set the [if_not_found] argument.

Good news: Google Sheets added XLOOKUP in 2023 with the same syntax, so your files move between the two without rewriting formulas.

When a formula does break, and the reason isn't obvious, Guidy can walk you through the fix on your own screen, pointing out the mismatch instead of leaving you to hunt for it.

FAQs

When did XLOOKUP come out, and which Excel versions support it?
XLOOKUP’s available for Microsoft 365 subscribers since 2020 in Microsoft 365, Excel 2021, Excel 2024, Excel for the web, and the Mac versions of those. Excel 2019, 2016, and earlier don't have it and return a #NAME? error.

Does Google Sheets have XLOOKUP?
Yes, Google Sheets has XLOOKUP. Google added XLOOKUP to Google Sheets in 2023, using the same syntax as Excel, =XLOOKUP(lookup_value, lookup_array, return_array), so files using it move between the two apps without breaking.

Can XLOOKUP return multiple values or columns at once?
Yes, it can. You can do so by setting the return array to several columns (for example, C2:E20). XLOOKUP spills the whole matching row across adjacent cells, pulling a name, price, and stock level from one formula. VLOOKUP can't do that in a single step.

What does XLOOKUP's "if_not_found" argument do?
The [if_not_found] argument sets what XLOOKUP gives when there's no match, such as "Not found" or 0. It replaces wrapping the formula in IFERROR: =XLOOKUP(A2, IDs, Prices, "Not found"). And it’s optional, not required.

Why is my XLOOKUP returning #N/A?
#N/A means that no match was found. In this case, you need to check for extra spaces, text stored as numbers, or a lookup value that isn't in the lookup array. You can also add [if_not_found] to show a friendly message, or use match_mode 2 for wildcard matches.