How to Use XLOOKUP in Excel (Step by Step)

By Dr. Zubair Khalid, DVM, MS, PhD ·

How to Use XLOOKUP in Excel (Step by Step)

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].

What Is XLOOKUP in Excel?

XLOOKUP is a built in Excel function that finds a value in one range and returns the matching value from another range. You write it as =XLOOKUP(lookup_value, lookup_array, return_array), and it returns an exact match by default, so a basic lookup needs only those three arguments [1]. It works in Excel for Microsoft 365, Excel 2021, Excel 2024, and Excel for the web, plus the Mac and mobile versions of those releases [2].

To use it, put the value you are hunting for in the first argument, the row or column to search in the second, and the row or column holding the answer in the third. For example, =XLOOKUP(C2,A2:A6,B2:B6) searches A2:A6 for whatever is in C2 and returns the item in the same position of B2:B6. The two ranges must be the same size, or you get a #VALUE! error.

The function reads in any direction. The return range can sit to the left of the search range, which VLOOKUP cannot do because it only reads rightward from the first column of its table [3]. That single change removes the column counting that trips people up in older formulas.

A few optional arguments change the behavior. The fourth argument sets what to show when nothing matches, such as "Not found" instead of #N/A. The fifth sets the match type: 0 for exact (the default), -1 for exact or next smaller, 1 for exact or next larger, and 2 for a wildcard match. The sixth sets the search direction: 1 searches top to bottom, -1 searches bottom to top, and 2 or -2 run a faster binary search on data that is already sorted.

What you want to doHow to set it up
Find an exact matchUse the three required arguments only
Show a friendly message when missingAdd a fourth argument such as "Not found"
Return a value from a column on the leftPoint the return range at the left hand column
Find the last of several matchesSet the sixth argument to -1
Match part of a text stringSet the fifth argument to 2 and use * or ?

If you are moving from an older function, the main adjustment is mental: there is no TRUE or FALSE flag to remember, because exact matching is already the default [1].

Syntax

The full syntax is:

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

ArgumentRequired?Meaning
lookup_valueRequiredThe value you want to find. It can be a number, text, or a reference to a cell.
lookup_arrayRequiredThe row or column you search in.
return_arrayRequiredThe row or column you return a result from. It must be the same size as lookup_array.
if_not_foundOptionalWhat to return when no match exists. If you leave it out, Excel returns #N/A.
match_modeOptional0 for exact match (the default), -1 for exact or next smaller, 1 for exact or next larger, 2 for a wildcard match.
search_modeOptional1 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.

ABCD
1Product IDPriceLookup IDResult
2P10110.99P103=XLOOKUP(C2,A2:A6,B2:B6) -> displays 15.75
3P10212.50P105=XLOOKUP(C3,A2:A6,B2:B6) -> displays 20.00
4P10315.75P101=XLOOKUP(C4,A2:A6,B2:B6) -> displays 10.99
5P1048.25P102=XLOOKUP(C5,A2:A6,B2:B6) -> displays 12.50
6P10520.00P104=XLOOKUP(C6,A2:A6,B2:B6) -> displays 8.25

Step by step:

  1. 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.
  2. Look up the price for product ID P105. In cell D3, the same formula finds P105 in A6 and returns 20.00.
  3. Look up the price for product ID P101. In cell D4, it finds P101 in A2 and returns 10.99.
  4. Look up the price for product ID P102. In cell D5, it finds P102 in A3 and returns 12.50.
  5. 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

ErrorLikely causeFix
#N/AThe 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 resultThe 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 identicalOne 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.

How to Do an XLOOKUP in Excel, Step by Step

The fastest way to learn the function is to build one formula from scratch and then reuse it. The steps below assume a simple two column list, but the same order applies to any sheet.

Step 1: Decide what you are looking for and what you want back

Write down the lookup value (the thing you know) and the return value (the thing you want). For a price list, you know a product code and want its price. For a staff roster, you know a name and want a phone number. Naming both before you type keeps the next two steps straight.

Step 2: Select the search range and the return range

Click the column or row that holds the lookup value first, then the one that holds the answer. Both must cover the same number of cells. If your search range is A2:A100, your return range must also be 99 cells tall, such as B2:B100. A mismatch is the most common cause of a #VALUE! error.

Step 3: Type the formula and point at cells, not typed values

Start with =XLOOKUP(, then click the cell holding the value to find, add a comma, drag over the search range, add a comma, and drag over the return range. Close the bracket and press Enter. Pointing at a cell rather than typing the value means you can copy the formula down a column and each row looks up its own item.

Step 4: Add a not found message and fill down

Add a fourth argument such as "Not found" so a missing item shows readable text instead of an error code. Then drag the fill handle down. Every row recalculates with its own lookup value while the two ranges stay fixed.

Step 5: Check one result by hand

Pick a row and confirm the returned value really sits beside the matched item in your source data. This catches reversed ranges and off by one selections before they spread through the sheet.

XLOOKUP Compared with the Older Lookup Functions

The table below sets the three common lookup approaches side by side on the points that matter most in daily use.

FeatureXLOOKUPVLOOKUPINDEX and MATCH
Default match typeExact [1]Approximate, unless you add FALSE [3]Exact, depending on the MATCH argument
Look left of the search columnYesNo [3]Yes
Separate column count argumentNoYes [3]No
Built in not found messageYesNoNo, unless wrapped in IFERROR
Works in Excel 2016 and 2019No [2]YesYes
Two way lookup in one functionYes, by nesting [2]NoYes, by combining the two

The practical takeaway: on a modern Excel release, XLOOKUP removes two of the most common VLOOKUP mistakes, the forgotten FALSE and the miscounted column index. On a shared workbook that must open in Excel 2016 or 2019, the older functions remain the safe choice because XLOOKUP will not calculate there [2].

Worked Example: A Grade Book with Approximate Matching

Approximate matching is where many learners stall, so here is a full walk through. Suppose column A holds score cutoffs in ascending order (0, 60, 70, 80, 90) and column B holds the matching letter grades (F, D, C, B, A). A student scored 85, and that score sits in cell D2.

The formula =XLOOKUP(D2,A2:A6,B2:B6,"No grade",-1) searches for 85, finds no exact entry, and because the fifth argument is -1 it steps back to the largest cutoff that is 85 or below, which is 80. It then returns the grade in the same position, B. A score of 90 returns A exactly, and a score below 0 returns the "No grade" message.

Two rules make this work. The cutoff list must be sorted from smallest to largest, and the fifth argument must be -1 for exact or next smaller. If you use 1 instead, the function looks for the next larger cutoff and returns the grade above the one the student earned. This is the same logic behind tax brackets and shipping weight bands, and it is worth testing with a score that falls exactly on a cutoff to confirm the boundary behaves as you expect.

Common Misconceptions About XLOOKUP

It does not replace every lookup job. It returns one result per formula. When several rows match, you get the first by default or the last if you set the sixth argument to -1. Pulling every match at once needs a different function such as FILTER.

It is not a version upgrade you can install. The function ships with the Excel release itself, so a workbook that uses it will show errors for anyone on Excel 2016 or 2019 [2]. There is no add in that adds it to those versions.

A wildcard match is not the same as a fuzzy match. Setting the fifth argument to 2 lets and ? stand in for characters, so "P10" finds any code starting with P10. It does not tolerate typos or near spellings.

Binary search is not a speed setting you can switch on safely. Search modes 2 and -2 assume the data is sorted. On unsorted data they can return a wrong answer with no error message, which is harder to spot than a visible failure.

The not found argument is not a match flag. People used to VLOOKUP sometimes type FALSE in the fourth position out of habit. In XLOOKUP that slot holds the value to display when nothing matches, so FALSE would be shown as the result rather than forcing an exact match.

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.

References

  1. Lookup and reference functions (reference) | Microsoft Support
  2. XLOOKUP function | Microsoft Support
  3. VLOOKUP function | Microsoft Support

Further Reading

Related Articles

Related articles