XLOOKUP(): Excel's Most Versatile Lookup Function

If you have ever used VLOOKUP() and got stuck because you needed to search to the left, XLOOKUP() solves that problem. It is Excel's newer lookup function. It can replace  VLOOKUP(), HLOOKUP(), and even most INDEX() and MATCH() formulas with one simple formula.

XLOOKUP() can search in any direction, pull back more than one column at a time, and show a custom message when a value is missing. 

In this guide, we will walk through the syntax first. Then we will build real formulas step by step, using one simple dataset the whole way through. We will also cover exact and approximate matches, how to return more than one column, how to handle errors, and how XLOOKUP() compares to VLOOKUP() and INDEX()/MATCH(). 

How to Use XLOOKUP() in Excel

Let's build a real XLOOKUP() formula from scratch. 

Our dataset has five products, each with an ID, a name, a category, and a price, from columns A through D, with headers in row 1.

Dataset for XLOOKUP(). Image by Author.

Say we want to find the price of "P104." Let's walk through how to do it step by step.

Look up a single value

Click an empty cell where you want the result to appear. I use F2. And type the following formula:

=XLOOKUP("P104", A2:A6, D2:D6)

In this formula: 

• "P104" is the lookup_value, the thing we searched for

• A2:A6 is the lookup_array, the range Excel searched in

• D2:D6 is the return_array, the range Excel pulled the answer from

Excel searches column A, “Product ID,” for "P104," finds it in row 5, and returns the corresponding value from column D: $45.

Instead of directly typing "P104", you can reference a cell, so you can change the lookup value without editing the formula itself. In our, cell A5 contains "P104," so the formula becomes:

=XLOOKUP(A5, A2:A6, D2:D6)

Use XLOOKUP() in Excel. Image by Author.

Now you can type any product ID into A5, and the price updates automatically.

Return a value from another column

This step shows the real difference between XLOOKUP() and VLOOKUP(). You are not locked into returning a value from a column to the right of your search column.

Let's say instead of price, you want the category for "P102." Same setup, just point the return_array to column C, “Category” instead:

=XLOOKUP(A3, A2:A6, C2:C6)

Here, A3 returns Home. If changed to A5, it updates to Electronics. Similarly, change the return_array again to pull the product name, price, or any other column, all using the same lookup_value and lookup_array.

XLOOKUP() return value from another column. Image by Author.

With VLOOKUP(), you'd count columns from your search column and adjust a column index number every time you wanted a different result. With XLOOKUP(), you only point at the column you want directly. 

Copy the formula across multiple rows

So far, we have looked up one value at a time. But most real spreadsheets need to look up many values at once, say, a full list of order IDs that each needs a matching price.

Here is how to set that up: Column A, starting at A2, already contains our list of Product IDs. We want the matching prices to appear next to each one, in column F.

To do so, click on F2 and type the formula, referencing A2 for the lookup value like this:

=XLOOKUP(A2, $A$2:$A$6, $D$2:$D$6)

Now click on F2 again, then drag the fill handle (the small square at the bottom right corner of the cell) down through the rest of your rows.

Excel copies the formula down, and each row now returns the correct price for its own Product ID. A2's ID gets its price in F2, A3's ID gets its price in F3, and so on.

Copy the formula down to other cells. Image by Author.

Notice the dollar signs around A2:A6 and D2:D6. This is called an absolute reference. It locks the range in place, so it does not shift when you copy the formula down. 

That matters here, since every row needs to search the same product list, just with a different lookup value each time.

How to Use Exact Match and Approximate Match in XLOOKUP()

By default, XLOOKUP() looks for an exact match. If it does not find one, it returns an error, unless you have set up the if_not_found argument we covered earlier. 

But XLOOKUP() can also find the closest match, using the match_mode argument. 

Exact match (Default)

You don’t need to type anything extra for this. Every formula we have built so far already uses exact match, since it is XLOOKUP()'s default behavior.

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6)

Excel searches for the exact value in A2, and only returns a result if it finds that precise match somewhere in the lookup range.

If you want to write it out explicitly, add a zero as the fourth argument:

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6, , 0)

XLOOKUP() returns an exact match. Image by Author.

Notice the space before the 0? That skips over if_not_found, since we are leaving it blank here, and moves straight to match_mode. Most of the time, you can leave this argument out entirely and let Excel default to exact match on its own.

Approximate match

Now, let's say your data does not have a value for every possible lookup. Think of a commission structure where sales reps earn different rates depending on which range their total sales fall into. You will not have a row for every dollar amount, just the starting point of each range.

This is where an approximate match helps. 

Set match_mode to -1, and XLOOKUP() finds the largest value that is less than or equal to your lookup value.

=XLOOKUP(C2, $A$2:$A$5, $B$2:$B$5, , -1)

Suppose your lookup range holds sales thresholds of $0, $5,000, and $10,000, each tied to a different commission rate. 

A rep with $7,500 in sales does not match any row exactly. With match_mode set to -1, XLOOKUP() finds $5,000, the largest threshold that does not exceed $7,500, and returns that row's commission rate.

Set match_mode to 1 instead, and XLOOKUP() flips the logic. It finds the smallest value that is greater than or equal to your lookup value. This is helpful for things like shipping weight brackets, where you round up to the next tier rather than down.

XLOOKUP() returns an approximate match. Image by Author.

Note: approximate match assumes your lookup range is sorted, ascending for -1, descending for 1. If it is not sorted correctly, XLOOKUP() can return the wrong result without throwing an error, which makes it worse. So, always double-check your sort order before relying on this.

How to Look Up Values to the Left with XLOOKUP()

VLOOKUP() only searches the leftmost column of your range and returns a value from a column to its right; it can't go the other way. Its third argument isn't a range at all; it's a column index counted from the left edge. 

There's no way to tell it "go one column left," so if the value you need is to the left of your search column, you're stuck rearranging your data or adding a helper column just to make the lookup work.

XLOOKUP() doesn't have this restriction, since you point it to separate ranges for the search and the return.

Take our product list: Product ID is in column A, Product Name in column B. 

Suppose you have a product name and want to find its ID; the reverse of what VLOOKUP() can do.

In C9, enter Bluetooth Speaker (a name that exists in the list). In E2, enter:

=XLOOKUP(C9, $B$2:$B$6, $A$2:$A$6)

XLOOKUP() searches column B for "Bluetooth Speaker," finds it in row 5, and returns "P104" from column A, even though column A is to the left of column B. 

XLOOKUP() searches the values to the left, unlike VLOOKUP(). Image by Author.

How to Return Multiple Columns with XLOOKUP()

XLOOKUP() can return several columns at once, in a single formula, using a feature called dynamic arrays. To do so, point return_array to a range instead of a single column, and XLOOKUP() spills the entire matching row across as many cells as needed. 

Let's try it with our product list. 

In C9, I type a Product ID, for example, "P104." In E2 I enter:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6)

Notice the return_array here is B2:D6, three columns wide, covering Product Name, Category, and Price. Once you press Enter, XLOOKUP() finds "P104" in column A, then returns Bluetooth Speaker, Electronics, and $45 all at once, spilling into E9, F9, and G9.

XLOOKUP() returns multiple columns with one formula. Image by Author.

This is what spill range means. E9 has the actual formula, and F9 and G9 fill in on their own, without formulas of their own. 

Click on F9, and the formula bar shows a grayed-out version of the original formula, not something typed there directly. Try typing into F9 while E9's spill is active, and Excel will throw a spill error. 

We will cover how to fix that in the Common Errors section.

How to Handle Missing Values in XLOOKUP()

By default, XLOOKUP() throws a #N/A error when it can't find a match. The if_not_found argument can fix this by showing what we want instead of a default error.  

Suppose in C9, I type a Product ID that does not exist in my data, like "P999."

Returning a custom message

In G2, I enter:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "Not Found")

Since "P999" does not exist in column A, XLOOKUP() skips the error entirely and returns "Not Found". Change the phrase you want, like "Check Product ID" or "No Match".

Display a custom message instead of the default error. Image by Author.

Returning a blank

Sometimes you want an empty cell with no text. For this, use two quotation marks with nothing between them, like this:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "")

You can use this when you're using this formula in a report where a stray "Not Found" would look messy next to a column of real values.

Display a blank cell instead of the default error. Image by Author.

Leaving it as a genuine error

If you skip if_not_found entirely, XLOOKUP() falls back to #N/A. This isn't always a bad thing. If you're building a formula for your own use and want to catch a typo or a missing entry immediately, seeing #N/A pop up is more helpful than a quiet blank cell that hides the problem.

Note: if_not_found only catches missing matches. If your lookup_array and return_array are different sizes, for instance, XLOOKUP() still throws a #VALUE! error, and if_not_found won't stop that one. We'll cover that specific case in the Common Errors section further down.

Advanced XLOOKUP() Features

Let’s look at the advanced features of XLOOKUP()

Search from bottom to top

Set search_mode to -1, and XLOOKUP() scans from the bottom of your range up, instead of from top to bottom. Use this when your data has duplicate values, and you want the most recent entry, not the first one.

Let's say in my dataset I have three duplicate Customer IDs. In cell E2, I enter the default formula that will use the top-to-bottom approach: 

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6)

This is calculated from the beginning of the table and displays the result from the first match. 

But if I want to search from the bottom, I will use this formula: 

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, -1)

In this formula, -1 tells Excel to start searching from the bottom of the dataset. And this will return 340, as that is the first match value from the bottom.  

XLOOKUP() scans data from bottom to top. Image by Author.

Binary search mode

Set search_mode to 2, and XLOOKUP() will switch from checking one row at a time to a binary search instead. 

Here's how it works: 

• It looks at the middle item in your range first. 

• If that's not a match, it throws away the half where the value can't be, then looks at the middle of what's left. 

• It keeps doing this, cutting the data in half each time, until it finds the match. 

• Set search_mode to -2, and it works the same way, just starting from the last item instead of the first.

Here in E2, enter:

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, 2)

This formula returns 200 instead, a completely different row.

Binary search mode gives the wrong result. Image by Author.

Because binary search only works if your lookup_array is sorted. Since our Customer ID column isn't sorted, it goes C100, C101, C100, C102, C100, and the binary search throws away the wrong half and gives a wrong match. 

Now let's see what happens when the data is actually sorted. 

Sort the same log by Customer ID, ascending (all three C100 at the beginning) and run the same formula again. 

Binary search mode works with sorted data. Image by Author.

This time, E2 returns 120, matching what a normal search would give you. That's the difference. Binary search only skips data it can safely rule out, and that only holds when the data is sorted to begin with. 

Nested XLOOKUP() formulas

Nest one XLOOKUP() inside another when a lookup's result needs to feed into a second lookup, instead of a fixed range.

Let’s say you want to find a price, but which column you pull it from depends on a separate lookup, a customer's pricing tier, for instance.

=XLOOKUP(C9, $A$2:$A$6, XLOOKUP(D9, $F$2:$F$4, $G$2:$I$4))

In this formula, the inner XLOOKUP() first figures out which column range to pull from, based on D9, then the outer XLOOKUP() uses that result as its return_array. 

Nested XLOOKUP(). Image by Author.

XLOOKUP() vs. VLOOKUP()

We've covered how XLOOKUP() works in detail. Here's its comparison against VLOOKUP().

There are two things worth a quick note: 

• VLOOKUP() defaults to approximate matching. If you forget the fourth argument, it won't show an error; it'll just return the wrong value silently.

• If you insert a column into a VLOOKUP() sheet, formulas quietly start pulling the wrong value. XLOOKUP() avoids this by referencing ranges directly rather than counting columns.

None of this makes VLOOKUP() obsolete. It still works fine for simple, static sheets. But for anything built going forward, XLOOKUP() is the safer default.

XLOOKUP() vs. INDEX() and MATCH()

Before XLOOKUP() existed, INDEX() and MATCH() together were the standard way to work around  VLOOKUP()'s limits. It uses two functions instead of one. 

• MATCH() finds the position of your lookup value. 

• INDEX() then uses that position to pull the result from wherever you point it: left or right, it doesn't matter.

=INDEX(D2:D6, MATCH(C9, A2:A6, 0))

Compare that to the XLOOKUP() version we've used throughout this article:

=XLOOKUP(C9, A2:A6, D2:D6)

XLOOKUP() only works in Microsoft 365 and Excel 2021 onward, as we covered earlier. If your workbook needs to open in Excel 2019 or 2016, or you're sharing with people on older versions, INDEX() and MATCH() still work everywhere.

There's also a smaller case. 

INDEX() and MATCH() separates "find the position" from "return the value," which helps in some complex nested formulas. This comes up rarely, so most readers won't need it.

Outside these two cases, XLOOKUP() is the better choice for a new workbook. 

Common XLOOKUP() Errors and How to Fix Them

Here's a quick reference for the issues you're most likely to come across and how to fix each.

#N/A errors

This is XLOOKUP()'s default response when it can't find a match. I ran into this earlier, where my lookup_value and lookup_array didn't line up, and XLOOKUP() had nothing to match against in column A.

To fix this: Double-check that your lookup_value actually exists in your lookup_array, spelled and formatted the same way. If missing matches are expected and normal, use the if_not_found argument to replace #N/A with something more useful, like "Not Found" or a blank cell, as we covered earlier. 

Incorrect lookup range

This happens when your lookup_array and return_array don't line up correctly: wrong rows, wrong columns, or ranges that don't match in size. 

Depending on which version of Excel or Sheets you're using, this can show up as a #VALUE! error, or sometimes as #N/A with a separate message explaining the real problem. Don't let the #N/A fool you into thinking it's a missing match. 

To fix this: Make sure lookup_array and return_array cover the same number of rows or columns. If lookup_array is A2:A5, return_array needs to span rows 2 through 5 as well, not rows 4 through 5 or any other mismatched range.

Data type mismatches

VLOOKUP() may throw an error because it expected a number but got text instead. XLOOKUP() can hit a similar issue. If your lookup_value is a number, but your lookup_array stores those same values as text, or the other way around, XLOOKUP() won't find a match, even though the values look identical on screen.

To fix this: Check whether your lookup_value and lookup_array use the same data type. Numbers stored as text usually show up left-aligned in a cell instead of right-aligned. Wrap your lookup_value in VALUE() to convert text to a number, or use TEXT() to do the reverse, depending on which direction the mismatch runs.

Spill errors

This happens when your return_array covers more than one column, but the cells XLOOKUP() needs aren't empty. Excel needs open space next to your formula cell to spill the results. If something's already there, the whole formula fails.

To fix this: Clear out the cells where the results need to go, then enter your formula. If you need something in those cells, move your XLOOKUP() formula somewhere else with open space around it.

Final Thoughts 

You now know how to use XLOOKUP() for single values, multiple columns, missing data, and even nested lookups across two ranges. That's a lot of ground covered.

Going forward, make XLOOKUP() your default for any new workbook. It's more flexible than VLOOKUP(), easier to read than INDEX/MATCH(), and it saves you from wrapping every formula in IFERROR().

If you want to go deeper, it's worth learning INDEX() and MATCH() on their own too.