XLOOKUP Example Sheet: Formula, Setup, and Tips

An XLOOKUP example sheet can find a value in one column and return the matching value from another. With product IDs in A2:A5, prices in C2:C5, and the ID to search in E2, enter =XLOOKUP(E2,A2:A5,C2:C5,"Not found") in F2; if E2 contains P-103, the result is 24.5. The example below shows how to lay out the data, adapt the formula, and handle common lookup problems in Excel for Microsoft 365 on Windows.

Set up a simple XLOOKUP example sheet

Start with a small, tidy list. Put field names in the first row and one record per row beneath them. For this example, enter the following data in A1:C5:

Product ID	Product	Price
P-101	Pen	1.25
P-102	Notebook	4.80
P-103	Desk lamp	24.50
P-104	Cable	7.95

Use E1 for the label “Product ID to find,” type P-103 in E2, and label F1 “Price.” The formula in F2 searches the ID column and returns the value from the price column. In this layout, the displayed result is 24.5; format F2 as currency if you want it to appear as $24.50. The lookup value can be typed directly into the formula, but putting it in a separate cell makes the sheet easier to reuse.

Keep the lookup data in a consistent format. For example, store every product ID as text, rather than mixing text IDs with numeric values. A simple layout also makes it easier for someone else to understand which column is searched and which column supplies the answer.

How the XLOOKUP formula works

XLOOKUP has the general structure XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). The first three arguments are the essentials: the value you want to find, the range to search, and the range containing the answer. Optional arguments let you specify a message for a missing match, use a non-exact match, or change the search direction.

For the example sheet, E2 contains the lookup value, A2:A5 is the lookup array, and C2:C5 is the return array. Because the function is not given a match mode, it looks for an exact match by default. XLOOKUP can search vertically or horizontally, and the lookup and return ranges do not have to be next to one another.

The ranges must correspond in size and shape. Here, A2:A5 and C2:C5 each contain four rows. If the formula searches five IDs but returns from a three-cell range, the ranges do not align and Excel can return an error. Use cell references rather than manually retyping the answer, so a changed price or lookup value updates the result automatically.

Choose exact, approximate, or wildcard matching

For IDs, invoice numbers, and names, exact matching is usually the right choice. It is XLOOKUP’s default, so the basic formula does not need an extra match-mode argument. Approximate matching is useful for thresholds such as shipping bands, grading scales, or commission tiers. XLOOKUP’s match_mode argument accepts 0 for exact match, -1 for exact match or next smaller item, 1 for exact match or next larger item, and 2 for wildcard matching.

For a tier example, put minimum quantities 0, 50, 100, and 200 in A2:A5, and the labels Starter, Bronze, Silver, and Gold in B2:B5. Put 135 in D2. With those cells arranged as described, this formula in E2 uses the next smaller threshold and returns Silver:

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

For wildcard matching, the lookup text can include * to represent any number of characters or ? to represent one character. Wildcards require match_mode 2. For instance, with names in A2:A5, employee IDs in B2:B5, and the search text Sam* in D2, enter =XLOOKUP(D2,A2:A5,B2:B5,"No match",2) in E2. It returns the ID for the first name beginning with Sam in the search order. This is not the same as a general “contains” search unless the asterisks are placed before and after the search text.

Return more than one value

XLOOKUP can return a range of columns for the matching record. This is handy when an answer needs several fields, such as a product name and its price, without writing a separate lookup formula for each field. In a separate example layout, put IDs in A2:A5, product names in B2:B5, prices in C2:C5, and the ID to find in E2. Enter this formula in F2:

=XLOOKUP(E2,A2:A5,B2:C5,"Not found")

If E2 is P-103, Excel returns Desk lamp in F2 and 24.5 in G2. In current Microsoft 365, the second result spills into the adjacent cell automatically. Keep G2 empty so it does not block the spilled result; a blocked spill area can cause a #SPILL! error. This kind of multi-column return is a useful alternative to copying and editing similar formulas across several result columns.

Use reverse searches and understand duplicates

If the lookup column has repeated values, a normal XLOOKUP returns the first match it encounters from the top. To retrieve the last matching record instead, set search_mode to -1, which searches from last to first. For example, suppose A2:A5 contains customer IDs and B2:B5 contains their latest status entries, with repeated IDs representing later updates farther down the list. Put the ID to find in D2, then enter this formula in E2:

=XLOOKUP(D2,A2:A5,B2:B5,"No record",0,-1)

The formula returns the status from the last row containing that ID. Reverse search changes the direction in which Excel checks the rows; it does not sort the data or determine which date is newest. If records are not ordered by date, use a deliberate sort or a separate date-based method rather than assuming the last row is the most recent record.

Fix common XLOOKUP problems

A missing result is often a data issue rather than a faulty formula. Check the lookup value and source column for extra spaces, inconsistent punctuation, or numbers stored as text in one place and as numbers in another. A leading zero in a code can also matter if one version is stored as text and another is treated as a number. Compare the source values carefully before changing the formula.

#N/A: No match was found. Check the spelling, value type, and selected lookup range. The if_not_found argument can replace the standard missing-match result with a helpful message.
#VALUE!: Check that the lookup and return arrays have compatible dimensions. For a vertical lookup, use ranges with the same number of rows.
#SPILL!: If the return array produces several values, clear any occupied cells in the spill area.
Unexpected match: Confirm whether the formula uses an approximate or wildcard match, and check for duplicate lookup values. Exact matching is the default, but optional arguments can change the behavior.

The if_not_found argument handles a value that has no match; it is not a universal error trap. For example, it will not automatically turn every possible formula error into a custom message. When debugging, temporarily remove that argument or inspect the source ranges so you can distinguish an absent value from a range or data problem.

How XLOOKUP compares with VLOOKUP and INDEX/MATCH

VLOOKUP searches the leftmost column of a table and returns a value from a column to its right. XLOOKUP lets you specify the lookup and return ranges separately, so the return column can be to the left or right of the lookup column. XLOOKUP also uses exact matching by default, whereas VLOOKUP commonly requires its final argument to be set deliberately for exact matching.

INDEX/MATCH can also look in one range and return from another, and it remains useful in older Excel versions that lack XLOOKUP. XLOOKUP often makes a straightforward lookup easier to read because the search and result ranges are stated directly in one function. There is no single formula that is best for every workbook: compatibility with the people who must open the file is an important factor.

Availability and version compatibility

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including current Excel for the web. The formula syntax shown here works in current Excel for Windows and Mac; the location of interface commands is not relevant to entering the function. Excel 2019 and Excel 2016 do not include XLOOKUP. If someone opens an XLOOKUP workbook in an unsupported version, they may see a function-name error rather than a calculated result.

Before sharing a workbook, consider the recipients’ Excel versions. If an older version must be supported, use a compatible alternative such as INDEX/MATCH or VLOOKUP, taking care to use exact-match settings where appropriate. Compatibility can also matter when a workbook is opened in another spreadsheet program, so check the recipient’s software rather than assuming every application implements Excel functions identically.

Build a reusable lookup sheet

Once the basic example works, make the sheet easier to maintain. Keep the source data together, use clear headers, and put user-entered search values in visibly separate cells. Avoid including header rows in the lookup ranges unless the lookup value could genuinely match a header. For larger lists, converting the source range to an Excel table can make the data easier to extend and manage, though the simple cell-reference formula is often clearest for a first example.

Test one known ID and verify the returned value against the source row.
Test a value that is absent to confirm the chosen not-found message appears.
Try a duplicate ID if duplicates are possible, and decide whether the first or last match is appropriate.
Leave enough empty space beside a formula that returns multiple columns.
Format returned values—such as prices or dates—for readability without changing the underlying lookup result.

These checks help turn a demonstration into a dependable worksheet. A lookup formula finds values according to the ranges and options it is given; it does not validate that the source data is complete, current, or free of duplicate records.

Frequently Asked Questions

What is the simplest XLOOKUP formula?

Use =XLOOKUP(value_to_find,range_to_search,range_to_return) as the basic pattern. Replace the placeholders with a cell reference and matching ranges. For a real workbook, adding an if_not_found message can make a missing match easier to recognize.

Does XLOOKUP find an exact match by default?

Yes. Its default match mode is exact, so you do not have to add 0 for an ordinary exact lookup. You can still include 0 explicitly when you want the formula to make that choice especially clear to readers.

Can XLOOKUP return a value from a column to the left?

Yes. Set the lookup_array to the column containing the search value and the return_array to the column containing the answer. Unlike VLOOKUP, the return column does not have to be to the right of the lookup column.

Why does XLOOKUP show #N/A?

Usually, the lookup value is not an exact match in the selected range. Check for spelling differences, extra spaces, mismatched text and number formats, or a range that does not include the relevant row. You can also set the optional if_not_found argument to display a message.

Can I use XLOOKUP in Excel 2019?

No. XLOOKUP is not included in Excel 2019 or Excel 2016. If a workbook must work in those versions, use an older compatible lookup approach, such as INDEX/MATCH or VLOOKUP, and verify the match settings.

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.