XLOOKUP Practice Examples for Everyday Excel Tasks

For an exact-match lookup, put the value to find in one cell, then use XLOOKUP to search a lookup range and return the corresponding value: =XLOOKUP(E2,A2:A6,C2:C6,"Product not found"). For example, if product IDs are in A2:A6, prices are in C2:C6, and E2 contains the ID, this returns that product’s price or the message if the ID is absent.

What XLOOKUP Does

XLOOKUP searches one range or array for a value and returns a related value from another range or array. It is useful for questions such as “What is the price for this product ID?” or “Which department belongs to this employee number?” Unlike older VLOOKUP formulas, XLOOKUP can return values from columns to the left or right of the search column, and it does not need a column number.

The function’s general syntax is =XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode]). The first three arguments are required. The optional fourth argument supplies a result when nothing matches; the fifth controls exact, approximate, or wildcard matching; and the sixth controls the search direction or method.

By default, XLOOKUP looks for an exact match and searches from the first item to the last. That default suits most everyday lists. Its return range can be a single column or row, or multiple adjacent columns or rows. When the formula returns several values, Excel may display them in neighboring cells as a spilled array; those cells need to be clear.

Practice 1: Find a Price by Product ID

Start with a small product list. Suppose A2:A6 contains IDs P-101, P-102, P-103, P-104, and P-105, while C2:C6 contains their prices 8.50, 14.00, 6.25, 22.00, and 9.75. Cell E2 contains P-103. With IDs in A2:A6, prices in C2:C6, and the requested ID in E2, enter =XLOOKUP(E2,A2:A6,C2:C6). The result is 6.25.

This is a useful first exercise because the lookup and return ranges are easy to identify. A2:A6 is the range XLOOKUP searches; C2:C6 is the range it returns a value from. The rows must correspond: the first price belongs to the first ID, the second price to the second ID, and so on. The ranges can be on different worksheets, but they still need compatible dimensions.

To make the lookup reusable, change E2 to another product ID. The formula updates without editing its ranges. If you copy it down a column, consider locking the data ranges with dollar signs. With IDs in A2:A6, prices in C2:C6, and lookup IDs in E2:E10, enter this in F2 and fill down: =XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6,"Product not found"). For E2 containing P-103, F2 returns 6.25; an absent ID returns the stated message.

Practice 2: Show a Helpful Message When Nothing Matches

If a lookup value is missing, XLOOKUP normally returns the #N/A error. The optional if_not_found argument lets a formula communicate what happened without wrapping it in IFERROR. With employee IDs in A2:A20, names in B2:B20, and the ID to search in E2, use =XLOOKUP(E2,A2:A20,B2:B20,"Employee ID not found"). If E2 contains an ID in the list, the corresponding name appears; otherwise the cell displays Employee ID not found.

This message is especially helpful in reports used by people who may not recognize spreadsheet errors. It can also be an empty string if a blank result is preferred. With the same employee layout, use =XLOOKUP(E2,A2:A20,B2:B20,"") to show a blank when there is no match. A blank can make a report cleaner, but it can also hide missing or mistyped IDs, so a descriptive message is often better while building or checking a workbook.

The not-found argument only handles the absence of a match. It does not correct a wrong range, a misspelled function, or other formula errors. Also check whether a value that looks identical has extra spaces or a different data type—for instance, a number stored as text. Those issues can prevent the expected exact match.

Practice 3: Return a Value to the Left or Return Several Columns

One advantage over VLOOKUP is that XLOOKUP does not require the return range to be to the right of the lookup range. Suppose employee IDs are in C2:C10 and employee names are in A2:A10, with the requested ID in E2. With that layout, enter =XLOOKUP(E2,C2:C10,A2:A10,"No employee"). If E2 contains an ID associated with the name Maya Chen, the formula returns Maya Chen, even though the name column is to the left.

XLOOKUP can also return multiple fields at once. Suppose product IDs are in A2:A6, product names in B2:B6, and prices in C2:C6. With the ID to find in E2, use =XLOOKUP(E2,A2:A6,B2:C6,"Not found"). If the matching row contains Notebook in B and 14.00 in C, the formula returns both values in adjacent cells. In Microsoft 365, the results spill from the formula cell; ensure the cells to its right are empty, or Excel may show a #SPILL! error.

When building a multi-column result, select the return range to include exactly the fields wanted and keep its rows aligned with the lookup range. A return range with a different number of rows can produce an error or an unintended result. This is also a convenient way to retrieve a record’s name, category, and price from one lookup rather than creating separate formulas for each field.

Practice 4: Use Approximate Match for Bands or Thresholds

Exact matches are not the only option. Approximate match is useful when a value belongs to a band, such as a commission rate based on sales. Suppose A2:A5 contains ascending thresholds 0, 500, 1000, and 1500, and B2:B5 contains the corresponding rates 0%, 2%, 4%, and 6%. With the sale amount in E2, use =XLOOKUP(E2,A2:A5,B2:B5,"No band",-1). For a sale of 1,200, the result is 4%, the rate attached to the largest threshold not exceeding 1,200.

The match_mode argument of -1 means exact match or next smaller item. A match_mode of 1 means exact match or next larger item. The default, 0, is exact match. For approximate banding, check the boundary values carefully: the formula above treats a value of 1,000 as the 4% band and a value below 500 as the 0% band. This example is only a spreadsheet illustration; real commission rules should be verified against the applicable plan.

For a threshold table, arrange the values so the intended bands are clear and test amounts at, below, and above each boundary. The search mode is separate from the match mode: match_mode selects what counts as a match, while search_mode determines how XLOOKUP searches the range.

Practice 5: Find a Partial Text Match or the Last Occurrence

Wildcard matching can locate text that contains a known fragment. Suppose A2:A8 contains item descriptions and B2:B8 contains supplier names. With the fragment in E2, such as “cable,” use =XLOOKUP("*"&E2&"*",A2:A8,B2:B8,"No description found",2). The result is the supplier for the first description containing that text. Match_mode 2 enables wildcard matching; an asterisk represents any number of characters, and a question mark represents one character.

Wildcard matching is useful for inconsistent descriptions, but it can match more than intended. If E2 contains a character that should be treated literally as a wildcard, prefix it with a tilde in the search pattern. XLOOKUP’s ordinary exact matching is not case-sensitive; use another approach if upper- and lowercase letters must be distinguished.

To find the most recent or last matching entry in a list, set the search_mode to -1. Suppose dates are in A2:A20, transaction IDs in B2:B20, and the requested ID is in E2. With that layout, enter =XLOOKUP(E2,B2:B20,A2:A20,"No transaction",0,-1). If the ID occurs on several rows, this returns the date from its last occurrence in the range. This returns the last matching row, not automatically the latest calendar date; that distinction matters if the rows are not ordered by date.

Practice 6: Match Two Conditions

Sometimes a value is identified by a combination, such as a product and a region. Suppose products are in A2:A10, regions in B2:B10, and quantities in C2:C10. Cell F2 contains the product and G2 contains the region. With those ranges and criteria, enter =XLOOKUP(1,(A2:A10=F2)*(B2:B10=G2),C2:C10,"No match"). If the row for the selected product and region has quantity 32, the formula returns 32.

Each comparison creates a series of TRUE or FALSE results. Multiplying the two comparison arrays turns rows meeting both conditions into 1, which XLOOKUP searches for; rows that fail either condition become 0. This technique is concise, but it assumes the pair identifies the intended row. If several rows share the same product-region combination, XLOOKUP returns the first match by default. To find the last one, add the search_mode argument -1 after the not-found and match-mode arguments.

Common XLOOKUP Problems and How to Fix Them

#N/A: No exact match was found, or the values differ in an easily missed way. Check spelling, spaces, number-versus-text formatting, and the lookup range. Add an if_not_found message when a missing result is expected.
#SPILL!: A formula returning multiple cells cannot place its results because one or more destination cells are occupied. Clear the cells in the spill area and check for merged cells.
Wrong row returned: Duplicate lookup values are present. The default search returns the first matching item. Decide whether the first or last occurrence is appropriate, or use a more specific lookup condition.
Unexpected approximate result: Check match_mode and the arrangement of threshold values. Confirm what should happen exactly at a boundary and test nearby values.
Copied formulas change ranges: Relative references move when copied. Add dollar signs to ranges that must stay fixed, such as $A$2:$A$20, while leaving the changing lookup cell relative if appropriate.
Incompatible range sizes: The lookup and return ranges need to align so each searched item corresponds to the intended returned item. Recheck their starting and ending rows.

When debugging, temporarily remove optional arguments and test a simple exact-match formula against one known item. Then add the not-found message, matching mode, or search direction as needed. This separates a range-selection problem from a more advanced matching issue.

XLOOKUP Compared with VLOOKUP and INDEX/MATCH

VLOOKUP searches the first column of a table and returns a value from a column to its right, usually specified by a numeric column index. If the table changes, that index can become misleading. XLOOKUP names the lookup range and return range separately, so it can search in either direction and is easier to read when the source layout changes.

INDEX/MATCH can also look left or right and remains useful in older Excel files. XLOOKUP often makes the same task more direct by combining the search and return steps in one function. There is no need to replace a working formula solely for its own sake: compatibility with other people’s Excel versions, workbook conventions, and existing formulas may matter more than using the newest function.

Availability and Good Habits

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. It is not available in Excel 2019 or Excel 2016. If a workbook containing XLOOKUP is opened in an unsupported version, that version may display the _xlfn prefix or an unsupported-function error instead of calculating the result. Ask the workbook’s recipients what Excel version they use before relying on XLOOKUP in a shared file.

In Microsoft 365 for Windows, enter the formula in a worksheet cell and press Enter; Excel calculates it. Excel for Mac and Excel for the web also support XLOOKUP in supported versions, though the interface and some keyboard shortcuts differ from Windows. The formulas here use commas as argument separators, as in the standard English-language examples. Some regional settings use semicolons in formulas, so Excel may require the separator configured for that installation.

Use exact match for IDs, names, and codes unless the task explicitly calls for bands or wildcards.
Label your lookup and return columns so future readers can check the ranges.
Use a meaningful not-found message during review, especially for lists where missing items need attention.
Test duplicates, blank cells, and boundary values rather than checking only one successful lookup.

Frequently Asked Questions

Does XLOOKUP return the first or last match? It returns the first match by default, searching from the top or left. Set search_mode to -1 to search from the last item toward the first and return the last match.

Can XLOOKUP search to the left? Yes. Choose the lookup range and return range separately. The return range can be on either side of the lookup range, provided the corresponding rows or columns line up.

Why does XLOOKUP return #N/A when the value appears to be there? The stored values may not be identical even if they look alike. Check for extra spaces, text stored instead of numbers, spelling differences, and whether the lookup range actually includes the matching cell.

Can XLOOKUP return more than one column? Yes. Set return_array to a multi-column range. In supported dynamic-array Excel versions, the result spills into adjacent cells, which must be empty.

Is XLOOKUP available in older Excel versions? It is available in Microsoft 365 and Excel 2021 or later, but not Excel 2019 or Excel 2016. For files that must work in older editions, consider a compatible alternative such as INDEX/MATCH or VLOOKUP.

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.