How to Use XLOOKUP in Excel (Step by Step)

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-10-03

XLOOKUP is an Excel function that searches a range or array for a value and returns the corresponding item from a second range or array. It replaces VLOOKUP and HLOOKUP in modern versions of Excel and returns exact matches by default [1]. This guide covers the syntax, a step-by-step example, and the errors you are most likely to see.

Quick Answer

XLOOKUP finds a lookup value in a lookup array and returns the item at the same position in a return array.
The basic form is =XLOOKUP(lookup_value, lookup_array, return_array).
Exact match is the default, so you do not need a fourth argument for normal lookups [1].
The return array can sit to the left of the lookup array, which VLOOKUP cannot do [2].
XLOOKUP is not available in Excel 2016 and Excel 2019 [2].

Syntax

The full syntax is:

$$=XLOOKUP(lookup\_value,\ lookup\_array,\ return\_array,\ [if\_not\_found],\ [match\_mode],\ [search\_mode])$$

Argument	Required?	Meaning
lookup_value	Required	The value you want to find. It can be a number, text, or a reference to a cell.
lookup_array	Required	The row or column you search in.
return_array	Required	The row or column you return a result from. It must be the same size as lookup_array.
if_not_found	Optional	What to return when no match exists. If you leave it out, Excel returns #N/A.
match_mode	Optional	0 for exact match (the default), -1 for exact or next smaller, 1 for exact or next larger, 2 for a wildcard match.
search_mode	Optional	1 to search first to last (the default), -1 to search last to first, 2 or -2 for a binary search on sorted data.

The first three arguments do the work in most formulas. The last three control what happens when the lookup fails or when you want something other than an exact match.

How It Works

XLOOKUP walks down the lookup array until it finds the lookup value. It notes the position of that match, then returns the item in the same position in the return array. If the lookup value appears more than once, the default search mode returns the first match it meets.

Because the two arrays are matched by position, they must be the same length. If lookup_array covers A2:A6, then return_array must cover five cells as well, such as B2:B6. A mismatch produces a #VALUE! error.

The default match mode is exact. That is a change from VLOOKUP, where the fourth argument defaults to an approximate match and you have to type FALSE to get an exact one [3]. With XLOOKUP you get exact matching unless you ask for something else.

If you want a custom message instead of #N/A when nothing matches, supply the fourth argument:

$$=XLOOKUP(C2,\ A2:A6,\ B2:B6,\ "Not found")$$

That formula returns the text "Not found" for any lookup value that is not in A2:A6.

Worked Example

The sheet below holds a small price list. Column A has product IDs, column B has prices, column C holds the IDs you want to look up, and column D holds the XLOOKUP formulas.

	A	B	C	D
1	Product ID	Price	Lookup ID	Result
2	P101	10.99	P103	`=XLOOKUP(C2,A2:A6,B2:B6)` -> displays 15.75
3	P102	12.50	P105	`=XLOOKUP(C3,A2:A6,B2:B6)` -> displays 20.00
4	P103	15.75	P101	`=XLOOKUP(C4,A2:A6,B2:B6)` -> displays 10.99
5	P104	8.25	P102	`=XLOOKUP(C5,A2:A6,B2:B6)` -> displays 12.50
6	P105	20.00	P104	`=XLOOKUP(C6,A2:A6,B2:B6)` -> displays 8.25

Step by step:

Look up the price for product ID P103. In cell D2, =XLOOKUP(C2,A2:A6,B2:B6) searches A2:A6 for P103, finds it in A4, and returns the value in the same position in B2:B6, which is 15.75.
Look up the price for product ID P105. In cell D3, the same formula finds P105 in A6 and returns 20.00.
Look up the price for product ID P101. In cell D4, it finds P101 in A2 and returns 10.99.
Look up the price for product ID P102. In cell D5, it finds P102 in A3 and returns 12.50.
Look up the price for product ID P104. In cell D6, it finds P104 in A5 and returns 8.25.

Every formula uses the same three arguments. Only the lookup value in column C changes, which is why you can write the formula once in D2 and fill it down.

More Examples

Return a value from a column to the left. Suppose column A holds prices and column B holds product IDs. VLOOKUP cannot read leftward because the lookup value must sit in the first column of the table [3]. XLOOKUP has no such rule. =XLOOKUP("P103",B2:B6,A2:A6) searches the IDs in B and returns the price from A.

Return a custom message when nothing matches. =XLOOKUP(C2,A2:A6,B2:B6,"Not found") shows "Not found" instead of #N/A when the ID is missing.

Use a wildcard match. Set match_mode to 2 and include or ? in the lookup value. =XLOOKUP("P10",A2:A6,B2:B6,"Not found",2) returns the price for the first ID that starts with P10.

Search from the bottom up. Set search_mode to -1 to find the last match instead of the first. This is useful when a list records the same ID several times and you want the most recent entry.

Nest two XLOOKUP functions for a two-way lookup. Microsoft's own example looks up a row label and a column label at once: =XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17)) [2]. The inner XLOOKUP picks the column, and the outer one picks the row. This does the same job as INDEX and MATCH together [2].

Sum a range between two matches. Microsoft shows =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10)), which sums all values between the two matched positions [2].

If you are still deciding between the two functions, the differences are covered in XLOOKUP vs VLOOKUP: Differences and When to Use Each. For a wider view of the options, see Excel Lookup Functions: VLOOKUP, XLOOKUP and INDEX-MATCH, and if you need the older approach, Excel INDEX MATCH: How to Look Up Values Step by Step walks through it.

Errors and How to Fix Them

Error	Likely cause	Fix
#N/A	The lookup value is not in the lookup array.	Check spelling and stray spaces, or add the if_not_found argument.
#VALUE!	lookup_array and return_array are different sizes.	Make both ranges the same number of rows or columns.
#NAME?	The function name is misspelled, or your Excel version does not support XLOOKUP.	Check the spelling. XLOOKUP is not available in Excel 2016 and Excel 2019 [2].
Wrong result	The lookup value appears more than once and you expected a different row.	Change search_mode to -1 to return the last match.
#N/A on numbers that look identical	One value is stored as text and the other as a number.	Convert both to the same data type.

Common Mistakes

Forgetting that XLOOKUP is exact by default. If you copy a VLOOKUP habit and add a fourth argument of FALSE, you are passing FALSE as the if_not_found value, not as a match flag. Leave the fourth argument empty for exact matching, or use it for a custom not-found message.
Using ranges of different lengths. =XLOOKUP(C2,A2:A6,B2:B10) fails because the arrays do not line up. Keep both ranges the same size.
Leaving the lookup value as text when the source is numeric. A product ID typed as text will not match the same ID stored as a number. Check the cell format on both sides.
Assuming it works in every version. XLOOKUP is not available in Excel 2016 and Excel 2019 [2]. If you share a workbook with people on those versions, they will see errors.
Sorting assumptions with binary search. Search modes 2 and -2 assume sorted data. If the data is not sorted, you get wrong answers with no warning. Stick with the default search mode unless you know the data is ordered.
Hardcoding the lookup value. Typing the ID directly into the formula means you have to edit the formula for every new lookup. Point at a cell instead so you can fill the formula down.

Limitations

XLOOKUP returns one matching item per formula. If several rows match your lookup value, you get the first one by default, or the last one if you set search_mode to -1. To pull back every match, you need a different approach such as the FILTER function.

The function also depends on your Excel version. It is not available in Excel 2016 and Excel 2019, so a workbook built with XLOOKUP will not calculate correctly for anyone on those releases [2]. If you need a formula that works everywhere, VLOOKUP or INDEX and MATCH remain the safer choice, even though they are more awkward to write.

Frequently Asked Questions

How does XLOOKUP work compared with VLOOKUP?

XLOOKUP searches a lookup array and returns the item at the same position in a return array. VLOOKUP searches only the first column of a table and counts across a fixed number of columns to find the result [3]. XLOOKUP can return values from either side of the lookup column and matches exactly by default [1].

Is XLOOKUP available in my version of Excel?

It is available in Excel for Microsoft 365, Excel 2024, Excel 2021, and Excel for the web, plus the Mac, iPad, iPhone, and Android versions of those releases [2]. It is not available in Excel 2016 and Excel 2019 [2]. If you open a workbook that uses it in an older version, the formula will not calculate.

How do I do an approximate match with XLOOKUP?

Set the fifth argument, match_mode, to -1 for an exact match or the next smaller item, or to 1 for an exact match or the next larger item. For example, =XLOOKUP(85,A2:A10,B2:B10,"Not found",-1) returns the value for the largest item that is 85 or below. This is how you handle grade bands, tax brackets, and similar step scales.

Why does my XLOOKUP return #N/A when the value is clearly there?

The usual causes are trailing spaces, a number stored as text, or a lookup array that does not cover the row you expect. Trim the source data and check that both the lookup value and the lookup array use the same data type. Adding a custom if_not_found message will at least make the failure visible instead of showing a raw error.

Can XLOOKUP return a whole row or column?

Yes. If the return array is wider than a single column, XLOOKUP spills the matching row across the cells to the right. The same applies to a multi-row return array, which spills downward. Make sure the cells next to the formula are empty, or you will see a #SPILL! error.

Once you have your lookup results in place, a chart is often the next step. How to Make a Chart in Excel (Step by Step) covers that, and How to Make a Box Plot in Excel (Step by Step) is useful when you want to see the spread of the values you just pulled together.