XLOOKUP Spreadsheet Examples for Everyday Excel Tasks

XLOOKUP finds a value in one range and returns the corresponding value from another range; for example, with item codes in A2:A6, prices in B2:B6, and a requested code in E2, use =XLOOKUP(E2,A2:A6,B2:B6,"Not found"). It returns the matching price, or “Not found” if the code is absent. The examples below show how to adapt that pattern for common Excel tasks.

What XLOOKUP does and where it works

XLOOKUP is a lookup function in Microsoft Excel for Microsoft 365 and Excel 2021 or later. It searches a specified lookup array and returns a value from a corresponding return array. Unlike older VLOOKUP formulas, the lookup column does not have to be the leftmost column in a larger table, and you specify the return range directly.

The function’s argument order is =XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode]). The first three arguments are required. The optional arguments let you set a message for missing matches, choose exact or approximate matching, and control the direction of the search. In a standard lookup, the function defaults to an exact match and searches from the first item to the last.

These examples use ordinary cell ranges and comma separators, as in Excel for Microsoft 365 on Windows. Excel for the web and current Mac versions also support XLOOKUP; the formulas work the same way. Menu and ribbon paths are not needed to enter a formula: select a cell, type or paste it into the formula bar or cell, and press Enter.

Example: look up a price by item code

This is the everyday pattern for retrieving a single related value. Assume A2:A6 contains product codes, B2:B6 contains product names, and C2:C6 contains prices. Cell E2 holds the code to search for. The formula uses the code range as the lookup array and the price range as the return array:

=XLOOKUP(E2,A2:A6,C2:C6,"Not found")

If E2 contains P-104 and that code’s row has a price of 24.99 in column C, the result is 24.99. If E2 contains a code that is not in A2:A6, the result is the text Not found. The fourth argument is optional, but providing a clear message is often more useful than an unexplained #N/A error.

The lookup and return ranges must correspond row by row and have compatible shapes. Here, the first item in A2:A6 is paired with the first item in C2:C6, the second with the second, and so on. If you add rows to a dataset, update the ranges or use an Excel Table so the formula can include new records more reliably.

Example: return several fields at once

XLOOKUP can return more than one column from the matching row. Suppose A2:A6 contains product codes, while B2:D6 contains product name, department, and price in that order. F2 contains the requested code. Use:

=XLOOKUP(F2,A2:A6,B2:D6,"No match")

If F2 matches the third code, Excel returns that row’s values from B:D: the name, department, and price. In a version with dynamic arrays, the results spill into the cells to the right of the formula. Leave those output cells empty; if they are occupied, Excel may show a #SPILL! error. This is useful when a report needs several attributes for one selected ID without writing a separate lookup formula for each field.

Spilled results are a feature of modern Excel, including Microsoft 365 and Excel 2021 or later. If you share the workbook with someone using an older Excel release that does not support XLOOKUP or dynamic arrays, the formulas may not calculate there. Confirm the recipient’s Excel version when compatibility matters.

Example: find a value in a sorted band

Approximate matching can assign a category based on thresholds, such as a rate for a sales band. Assume A2:A5 contains ascending minimum sales amounts of 0, 10,000, 25,000, and 50,000, and B2:B5 contains the corresponding rates of 3%, 5%, 8%, and 10%. E2 contains sales of 32,000. Use:

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

The result is 8%, because 25,000 is the largest threshold that is less than or equal to 32,000. The match mode -1 means “exact match or next smaller item.” Keep thresholds sorted in ascending order so the bands are easy to check and maintain. If E2 is below the lowest threshold, the formula returns No tier.

Approximate matching is not the same as rounding to the nearest value. For instance, a value of 24,900 falls into the 10,000 band, not the 25,000 band. Check the boundary values in your own table to make sure the chosen direction matches the rule you intend to apply.

Example: return the last matching record

By default, XLOOKUP searches from the top of the lookup array downward. Its search mode can instead make it search from the last item upward, which helps when an ID appears repeatedly and you want the value on its last matching row. Suppose A2:A100 contains customer IDs, C2:C100 contains status values, and F2 contains the customer ID to find:

=XLOOKUP(F2,A2:A100,C2:C100,"No record",0,-1)

The result is the status in the last row containing that customer ID. The 0 explicitly requests an exact match; -1 as the sixth argument searches from last to first. “Last” means last in the current row order, not necessarily the newest by date. If you need the most recent record, make sure the rows are ordered by date as intended or build the lookup around a date-aware method.

Example: search with a wildcard

Wildcard matching can find text that fits a pattern rather than a complete exact value. For example, if A2:A20 contains codes and B2:B20 contains descriptions, and F2 contains the suffix A, the following searches for a code ending in -A:

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

The match mode 2 enables wildcard matching; the asterisk stands for any number of characters. A question mark represents one character, and a tilde can be used to escape a wildcard when you need to search for it literally. Wildcards are useful for text searches, but can return an unintended result if multiple entries fit the pattern: by default, XLOOKUP returns the first matching item.

Example: retrieve a value from a two-way table

A two-way lookup selects a row and a column, such as a monthly figure for a chosen region. Assume A2:A5 lists regions, B1:D1 contains month names, and B2:D5 contains the values. G2 holds the region and H2 holds the month. Nest one XLOOKUP inside another:

=XLOOKUP(H2,B1:D1,XLOOKUP(G2,A2:A5,B2:D5))

The inner lookup finds the selected region and returns its row of values from B:D. The outer lookup then finds the selected month in the header row and returns the value in that position. If G2 is West and H2 is Mar, the result is the West row’s March value. Use consistent labels in the row and column headers; a spelling or spacing mismatch can prevent a match.

Common XLOOKUP problems and how to fix them

A formula can look plausible and still fail because of range, data, or output issues. Check these points before replacing the lookup with a more complicated formula:

#N/A: No match was found and no custom not-found result was supplied. Check spelling, spaces, data types, and whether the lookup value is actually in the search range. Add an if_not_found message when a missing result is expected.
Wrong result: Repeated lookup values return the first match by default. Use the reverse search mode when the last row is required, and confirm what “last” means in the order of your data.
#VALUE! or an unexpected result: Check that the lookup array and return array line up and have compatible dimensions. A vertical lookup range paired with an unrelated or mis-sized return range is a common cause of errors.
#SPILL!: A multi-column or multi-row result needs empty cells for its output. Clear cells in the spill area, or return just the single field the report needs.
Numbers that do not match: A number stored as text is not always equivalent to a true numeric value. Check the source data’s types and clean or convert inconsistent entries rather than assuming the lookup is at fault.

For a large workbook, avoid unnecessarily broad ranges if a smaller range or Excel Table can express the dataset clearly. Tables also expand when new rows are added, helping prevent a lookup from quietly missing recently appended records. Clear labels and consistent source data usually matter more than making the formula longer.

XLOOKUP compared with VLOOKUP and INDEX/MATCH

VLOOKUP searches the first column of a table and returns a value from a numbered column to its right. This means inserting or rearranging columns can make a VLOOKUP’s column number point somewhere unexpected, and returning a value to the left of the lookup column takes extra work. XLOOKUP specifies the lookup and return arrays separately, so it can look left or right and is easier to read in many common cases.

INDEX/MATCH is another established way to perform flexible lookups and remains useful in older Excel versions that lack XLOOKUP. XLOOKUP often replaces the combination with one function, and its optional arguments include not-found handling and search direction. The best choice can depend on compatibility: if a workbook must work in older Excel releases, check whether those releases support the functions you use before distributing it. XLOOKUP is not available in Excel 2019 or Excel 2016.

Frequently Asked Questions

Does XLOOKUP return an exact match by default?
Yes. Its default match mode is exact, so you usually do not need to specify a match-mode argument for a standard lookup. Add one only when you want an approximate or wildcard match.

Can XLOOKUP return a value to the left of the lookup column?
Yes. The lookup array and return array are separate arguments, so the return range can be to the left or right of the search range. Make sure the two ranges still align by row.

Why does XLOOKUP show #N/A?
It normally means Excel did not find the lookup value and the formula did not provide a different not-found result. Check the value and source data for differences such as extra spaces, inconsistent number formats, or a missing record.

Can XLOOKUP return multiple columns?
Yes. Set the return array to include the columns you want. In modern Excel, the results spill into adjacent cells, so those cells must be clear.

Is XLOOKUP available in every Excel version?
No. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not Excel 2019 or Excel 2016. If you need to support older installations, consider a compatible alternative such as INDEX/MATCH.

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.