XLOOKUP Calculation Examples: Formulas That Work in Excel

XLOOKUP can find a matching value and return a related value, or calculate a result from the matched row. For example, use =XLOOKUP(E2,A2:A6,B2:B6,"Not found") to find the value in E2 in A2:A6 and return the corresponding entry from B2:B6; the examples below show how to adapt that pattern for calculations, multiple results, and approximate matches.

What XLOOKUP does

XLOOKUP searches one range or array for a value, then returns a value from a corresponding position in another range or array. It is often used to retrieve a price, name, date, or other field by an ID. Unlike older VLOOKUP formulas, it can look in either direction, and its default behavior is an exact match.

The function’s arguments are the value to find, the range to search, and the range to return a result from. Optional arguments let you specify what to show when no match exists, whether to use an exact or approximate match, and which direction to search. In the formula above, the ranges are the same height, and the first item in the return range corresponds to the first item in the search range.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including Excel for the web. A workbook that uses it may show a compatibility error when opened in older Excel releases that do not support the function. The examples here use comma argument separators, as in standard Microsoft 365 Excel for Windows; regional settings can use different separators.

Basic exact-match example

Suppose a small product list has product codes in A2:A6 and prices in B2:B6. Cell E2 contains the code you want to look up. If the code in E2 is P-104 and that row’s price is 18.50, the formula returns 18.50:

With product codes in A2:A6, prices in B2:B6, and the lookup code in E2, use:

=XLOOKUP(E2,A2:A6,B2:B6,"Not found")

The fourth argument supplies a clear message if the code is absent, rather than displaying the usual #N/A error. If the code is present, XLOOKUP returns the value in the same relative position in B2:B6. Because exact matching is the default, you do not need to add an exact-match setting for this common case.

Keep the search and return ranges aligned. If the first range starts in row 2, the corresponding return range should also start in row 2. A range that is shifted by one row can produce a valid-looking but incorrect result.

Calculate a result from two looked-up values

A lookup can be part of a larger calculation. For example, an order table may list product codes in A2:A5, quantities in B2:B5, and unit prices in C2:C5. If E2 contains a product code, you can look up its quantity and price and multiply them to calculate the line total.

With product codes in A2:A5, quantities in B2:B5, unit prices in C2:C5, and the requested code in E2, use:

=IFNA(XLOOKUP(E2,A2:A5,B2:B5)*XLOOKUP(E2,A2:A5,C2:C5),"Product not found")

If E2 is P-03, and its row contains quantity 4 and unit price 12.50, the result is 50.00: 4 multiplied by 12.50. IFNA displays the message if either lookup cannot find the code. This approach is easy to follow when the values needed for the calculation are stored in separate columns.

For a table with many rows, make sure each product code identifies the intended row. XLOOKUP returns the first matching item when searching from the beginning, so duplicate codes can make a lookup ambiguous. If the latest matching record is what you need, see the last-match example below.

Calculate the return values inside XLOOKUP

You can also give XLOOKUP a calculated return array instead of a single return column. Consider product codes in A2:A4, base prices in B2:B4, and tax rates in C2:C4. The expression that multiplies each price by one plus its tax rate creates calculated return values for the lookup.

With product codes in A2:A4, base prices in B2:B4, tax rates in C2:C4, and the requested code in E2, use:

=XLOOKUP(E2,A2:A4,B2:B4*(1+C2:C4),"Item not found")

If E2 is SKU-02 and its base price is 20 with a tax rate of 8%, the result is 21.60: 20 multiplied by 1.08. This keeps the lookup and calculation in one formula. The return expression must produce values that line up with the search range; mismatched array dimensions can cause an error.

This pattern is useful for straightforward row-by-row arithmetic, but it can be harder to audit than a helper column when the calculation is complicated. A helper column makes each intermediate amount visible and can make a workbook easier for another person to maintain.

Return several columns at once

If you want multiple fields for one record, XLOOKUP can return a range of columns. For instance, a table might hold product codes in A2:A5, descriptions in B2:B5, prices in C2:C5, and stock counts in D2:D5. The result can spill into the cells to the right of the formula.

With product codes in A2:A5, descriptions in B2:B5, prices in C2:C5, stock counts in D2:D5, and the lookup code in F2, enter this formula in G2:

=XLOOKUP(F2,A2:A5,B2:D5,"Not found")

If the matching row contains “Desk lamp,” 24.99, and 18, those three values fill G2:I2, provided the spill cells are empty. If a cell in the intended output area is occupied, Excel may show a #SPILL! error. Clear the obstructing cells or move the formula to an area with room. This is a dynamic-array behavior in current Excel versions; the formula itself is entered once.

Use XLOOKUP for a two-way lookup

A two-way lookup selects an item by both a row label and a column heading. Imagine a grid with regions in A2:A4, month headings in B1:D1, and sales figures in B2:D4. You want the sales for the region named in F2 and the month named in G2. Nesting XLOOKUP lets the inner lookup select a month column and the outer lookup select a region within that column.

With regions in A2:A4, month headings in B1:D1, sales values in B2:D4, the target region in F2, and the target month in G2, use:

=XLOOKUP(F2,A2:A4,XLOOKUP(G2,B1:D1,B2:D4))

If F2 contains North, G2 contains Mar, and the North–Mar intersection is 125, the result is 125. The inner function returns the selected month’s column from the sales grid; the outer function finds the selected region in that column. Check that the month headings and region labels match the text in F2 and G2. Extra spaces or inconsistent labels can prevent a match.

Approximate matches for thresholds

XLOOKUP can match a value to a threshold when an exact value is not present. For example, a table might list ascending sales thresholds in A2:A5 and corresponding rate values in B2:B5. The match mode set to -1 requests an exact match or, if there is none, the next smaller item. That is appropriate when each threshold marks the beginning of a band.

With ascending thresholds in A2:A5, corresponding rates in B2:B5, and the amount to classify in E2, use:

=XLOOKUP(E2,A2:A5,B2:B5,"No band",-1)

If the thresholds are 0, 1,000, 5,000, and 10,000, and E2 is 4,200, the selected threshold is 1,000. If the rate beside that threshold is 3%, the formula returns 3%. The thresholds should be arranged in ascending order for this banding logic to be clear and reliable. This formula returns a rate; it does not calculate a charge from the rate unless you add a separate multiplication.

For the opposite kind of threshold rule, where you need the next larger value when an exact match is absent, use match mode 1. Consider the meaning of the bands before choosing: using the wrong direction can assign a value to an unintended threshold.

Return the last matching record

When a search range contains repeated IDs, the default search begins at the first item. To retrieve the most recent matching entry in a list ordered from oldest to newest, set the search mode to -1. This searches from the last item toward the first.

With transaction IDs in A2:A6, corresponding statuses in B2:B6, and the ID to check in E2, use:

=XLOOKUP(E2,A2:A6,B2:B6,"Not found",0,-1)

If the ID appears in rows 2 and 5 and the status in row 5 is “Closed,” the formula returns “Closed.” The zero explicitly requests an exact match, while the final -1 sets the reverse search direction. This finds the last matching row in the range; it does not independently verify that the list is sorted by date or that the last entry is genuinely the newest.

Wildcards and common problems

For text searches, wildcard matching can find values that contain or follow a pattern. In wildcard mode, an asterisk represents any number of characters, a question mark represents one character, and a tilde can escape a wildcard character when you need to search for it literally.

With customer names in A2:A20, account numbers in B2:B20, and a search fragment in E2, use:

=XLOOKUP("*"&E2&"*",A2:A20,B2:B20,"No match",2)

If E2 contains east, the formula can match a name such as “North East Office” and return the account number from the same row. The ampersands join the wildcard characters to the text in E2. Match mode 2 enables wildcard matching; without that setting, the asterisks are treated as ordinary characters. Text matches are generally not case-sensitive.

When a result seems wrong or an error appears, check these common causes:

#N/A: The lookup value may not exist, or the values may differ because of spaces, spelling, or numbers stored as text. An optional not-found message can make missing records easier to spot.
Unexpected result: Duplicate lookup values return the first match by default. Use reverse search only when the last occurrence is the intended one.
#SPILL!: A cell blocks a multi-column result. Clear the output area or place the formula elsewhere.
Wrong approximate band: Confirm the match mode and the order and meaning of the threshold values.
Function not recognized: The workbook may be open in an Excel version that does not support XLOOKUP. Use Microsoft 365 or Excel 2021 or later, or choose a compatible alternative.

XLOOKUP compared with older lookup methods

VLOOKUP is familiar and works in older Excel versions, but it searches the first column of a table and returns a value from a column to its right. Its column-number argument can also become fragile if columns are inserted or rearranged. XLOOKUP separates the search range from the return range, so it can return values to the left or right and does not require a column index number.

INDEX and MATCH can also perform flexible lookups and may be useful when a workbook must support older Excel releases. XLOOKUP is often more readable for a single lookup because the search and return ranges are explicit in the same function. The best choice depends on the workbook’s required compatibility and on how other people will use it.

One misconception is that XLOOKUP automatically performs every calculation needed for a report. It retrieves a matching result, and it can return an expression calculated across a range, but it does not replace decisions about duplicate records, missing values, tax rules, or how thresholds should work. Make those rules explicit in the workbook.

Frequently Asked Questions

Does XLOOKUP return an exact match by default?
Yes. Its default match mode is exact match. You can supply a not-found message as an optional argument to show something more helpful than #N/A when there is no match.

Can XLOOKUP multiply or otherwise calculate a result?
Yes. You can multiply two values returned by separate XLOOKUP functions, or give XLOOKUP a calculated return array, such as a price range multiplied by a tax-adjustment array. Choose the pattern that is easiest to check and maintain.

How do I make XLOOKUP return the last match?
Use its search-mode argument with -1 to search from the last item toward the first. This is useful when repeated IDs are listed over time, but the order of the records must support the meaning you intend.

Can XLOOKUP return more than one column?
Yes. Use a multi-column return range. In supported modern Excel versions, results spill into neighboring cells, so leave the output area clear.

Why does XLOOKUP show #N/A when the item looks present?
Check for extra spaces, inconsistent characters, or a number stored as text in one range but as a number in another. Also confirm that the lookup and return ranges align and that the requested value is actually in the search range.

Does XLOOKUP work in older Excel versions?
XLOOKUP requires Microsoft 365 or Excel 2021 or later. For older releases, consider a compatible method such as INDEX and MATCH or VLOOKUP, taking their range and direction limitations into account.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.