XLOOKUP Function

Excel Guides / Lookup & Reference

Lookup & Reference

Find a value in one range and return the matching value from another, in any direction, with a built-in fallback.

Updated 12 Oct 2026 Ā· 7 min read Ā· By Sky Dashboards

XLOOKUP searches one range for a value and returns the matching item from another range. It replaces VLOOKUP, HLOOKUP and most INDEX MATCH formulas with a single function that defaults to an exact match, looks in any direction and lets you decide what to show when nothing is found. If you only learn one lookup function, make it this one.

XLOOKUP syntax

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

Argument Required? What it does
lookup_value Required The value to find, such as a SKU, a name or a date. Usually a cell reference.
lookup_array Required The single row or column to search.
return_array Required The range to return a value from. It must be the same height as lookup_array (or the same width, for a horizontal lookup). It can be more than one column wide.
if_not_found Optional What to return when there is no match, such as "Not found" or 0. Leave it out and XLOOKUP returns #N/A.
match_mode Optional How to match: 0 exact (the default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard.
search_mode Optional Which way to search: 1 first to last (the default), -1 last to first, 2 binary search on ascending data, -2 binary search on descending data.

Only the first three arguments are required. A basic lookup reads like a sentence: find this, in here, and bring back the value from there.

match_mode What it does
0 Exact match. Returns #N/A, or your if_not_found value, when there is none. This is the default.
-1 Exact match, or the next smaller item if there is no exact match.
1 Exact match, or the next larger item if there is no exact match.
2 Wildcard match, where *, ? and ~ have special meaning.
search_mode What it does
1 Search from the first item to the last. This is the default.
-1 Search from the last item to the first. Use it to find the most recent entry.
2 Binary search. The lookup array must be sorted in ascending order.
-2 Binary search. The lookup array must be sorted in descending order.

Note

Current Microsoft 365 builds also accept 3 as a match_mode for regular expression matching. The four modes in the table work in every version that has XLOOKUP.

Exact match: the everyday lookup

Here is a candle shop's product list. To find the price of the SKU typed in F2, search column A and return column C:

G2fx=XLOOKUP(F2,A2:A6,C2:C6,"Not found")
A B C D E F G
1 SKU Product Price Stock Find SKU Price
2 CN-101 Soy candle $24.00 38 CN-103 $32.00
3 CN-102 Wax melt $9.00 120
4 CN-103 Reed diffuser $32.00 15
5 CN-104 Gift set $35.00 0
6 CN-105 Room spray $18.00 42

XLOOKUP finds CN-103 in column A and returns the price from the same row of column C.

Type CN-999 in F2 and the formula shows Not found instead of #N/A. That fourth argument saves wrapping the formula in IFERROR, and because it only catches missing values, a genuine mistake in the formula still shows up.

Lock the ranges before copying

If you copy the formula down a column, make the ranges absolute so they do not slide: =XLOOKUP(F2,$A$2:$A$6,$C$2:$C$6). Click a reference in the formula bar and press F4 to add the dollar signs. More in absolute and relative references.

Look left and return several columns

VLOOKUP can only return columns to the right of the one it searches. XLOOKUP has no such limit because the lookup and return ranges are separate. To get the SKU for a product name, search column B and return column A:

=XLOOKUP("Gift set",B2:B6,A2:A6)

Returns CN-104, from the column to the left of the one being searched.

Make return_array wider than one column and XLOOKUP returns that whole slice of the row. In Microsoft 365 and Excel 2021 or later, the results spill into the cells to the right:

B8fx=XLOOKUP(A8,A2:A6,B2:D6)
A B C D
1 SKU Product Price Stock
2 CN-101 Soy candle $24.00 38
3 CN-102 Wax melt $9.00 120
4 CN-103 Reed diffuser $32.00 15
5 CN-104 Gift set $35.00 0
6 CN-105 Room spray $18.00 42
7
8 CN-103 Reed diffuser $32.00 15

One formula in B8 returns the product, price and stock for the SKU in A8.

Watch out

A spilled result needs empty cells to land in. If anything sits in C8 or D8, the formula shows #SPILL! until you clear them.

It works the other way too. Point lookup_value at a range, such as F2:F4, and XLOOKUP returns one result per value, spilling down the column.

Approximate match for tiers and commission bands

Some lookups should not need an exact hit. A commission scheme pays the rate for the band a sale falls in, so you want the threshold at or just below the sales figure. That is match_mode -1:

F2fx=XLOOKUP(E2,$A$2:$A$5,$B$2:$B$5,,-1)
A B C D E F
1 Sales from Rate Rep Sales Rate
2 $0 0.0% Aisha $13,400 5.0%
3 $5,000 3.0% Ben $4,750 0.0%
4 $10,000 5.0% Chloe $21,000 7.5%
5 $20,000 7.5% Dev $10,000 5.0%

Each rep's sales are matched to the highest threshold that does not exceed them. Dev's $10,000 lands exactly on a threshold, so it gets that band's rate.

The empty argument between the two commas skips if_not_found. Aisha's $13,400 has no exact match, so XLOOKUP steps down to the $10,000 band and returns 5.0%. Unlike VLOOKUP's approximate match, the band table does not have to be sorted, although keeping it in order makes it easier to check.

Setting match_mode to 1 does the opposite and returns the next larger item. Use it for questions such as which is the smallest box that fits an order, when the table lists box capacities.

Tip

Keep the bands in a small table on the sheet rather than typing them into the formula. When the scheme changes, you edit the table and every rate updates.

Find the last match

XLOOKUP searches from the top and stops at the first match. When a list holds repeat entries, such as an order log, you often want the most recent one instead. Set search_mode to -1 to search from the bottom up:

F2fx=XLOOKUP(E2,B2:B6,C2:C6,"None",0,-1)
A B C D E F
1 Date Customer Amount Last order for Amount
2 02-Oct-26 Mia $45.00 Leo $75.00
3 03-Oct-26 Leo $120.00
4 05-Oct-26 Mia $60.00
5 08-Oct-26 Sam $32.00
6 09-Oct-26 Leo $75.00

Leo appears twice. Searching last to first returns his 9 October order, not the one from 3 October.

This relies on the log being in date order, which it usually is when new rows go in at the bottom.

Note

Search modes 2 and -2 use a binary search, which is faster on very long sorted lists. If the data is not sorted the way you say it is, they can return wrong answers with no error, so stick with the default unless the list is huge and its sort order is guaranteed.

Two-way lookup with nested XLOOKUP

A two-way lookup finds the value where a row and a column meet, such as one product's sales in one month. Nest one XLOOKUP inside another. The inner one picks the month column, and the outer one picks the product row from that column:

H2fx=XLOOKUP(F2,A2:A4,XLOOKUP(G2,B1:D1,B2:D4))
A B C D E F G H
1 Product Jul Aug Sep Product Month Sales
2 Candles $4,200 $3,850 $5,100 Melts Aug $1,640
3 Melts $1,320 $1,640 $1,980
4 Diffusers $2,760 $2,410 $3,050

The inner XLOOKUP returns the whole Aug column, C2:C4. The outer XLOOKUP finds Melts in column A and returns the matching value from that column: $1,640.

Put a drop-down list in F2 and G2 and you have a simple selector. The month picker on a Sky Dashboards template works the same way: change one input cell and every tile that reads from it updates.

Wildcard matches

XLOOKUP does not treat * and ? as wildcards unless you set match_mode to 2. Then * stands for any run of characters, ? for any single character, and ~ in front of either one makes it literal.

=XLOOKUP("*diffuser*",B2:B6,C2:C6,"No match",2)

Returns $32.00, the price of the first product whose name contains "diffuser".

=XLOOKUP("*"&F2&"*",B2:B6,C2:C6,"No match",2)

The same idea with the search text in F2. Typing spray returns $18.00.

Matching ignores case, so Spray, spray and SPRAY all find Room spray. A wildcard search returns the first hit, so be specific when several names share a word.

Which Excel versions have XLOOKUP

XLOOKUP is available in Excel for Microsoft 365 on Windows and Mac, Excel 2021, Excel 2024, Excel for the web and the Excel mobile apps. It is not in Excel 2019, 2016 or earlier. Open a file that uses it in one of those versions and the formula appears with an _xlfn. prefix and returns #NAME? when it recalculates.

If you share files with people on older versions, use INDEX MATCH instead. It handles exact, approximate, left and two-way lookups, and it works in every version.

Frequently asked questions

Is XLOOKUP better than VLOOKUP?

For most jobs, yes. It defaults to an exact match, can return columns to the left, keeps working when you insert columns, and has a built-in not-found value. VLOOKUP is still worth knowing because older files and older versions rely on it.

Is XLOOKUP case-sensitive?

No. To force a case-sensitive match, look up TRUE in an EXACT test: =XLOOKUP(TRUE,EXACT(A2:A6,F2),C2:C6).

Why does XLOOKUP return #VALUE!?

Usually because lookup_array and return_array are different sizes, for example A2:A50 and C2:C49. Make both ranges start and end on the same rows.

How do I return every match, not just the first?

XLOOKUP returns one match per lookup value. To list every matching row, use FILTER.

Want the finished dashboard instead?

My Excel templates come with these formulas already wired up. Pick one, type in your own rows and the charts update.

Browse templates ā†’

Related guides

All 32 Excel guides