XLOOKUP in Excel: Practical Lookup Examples, Tips, and Uses

Examples of XLOOKUP in Excel include finding a customer name from an ID, returning the latest matching price, retrieving several fields at once, and assigning a grade from score thresholds. In Microsoft 365 for Windows, the basic pattern is to look for a value in one range and return the corresponding value from another; exact matching is the default.

What XLOOKUP Does

XLOOKUP searches a lookup range for a value and returns a related value from a return range. Unlike older VLOOKUP formulas, it can search in either direction, does not require the lookup column to be the first column in a table, and lets you specify what to display when there is no match.

The function has six arguments: the value to find, the range to search, the range to return from, an optional result for a missing value, an optional match mode, and an optional search mode. The first three arguments are enough for many everyday tasks. By default, XLOOKUP looks for an exact match and searches from the first item to the last.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It is not available as a built-in function in Excel 2019 or Excel 2016, so a workbook using it may show a name error in those versions. The examples below use standard comma separators in formulas; some regional Excel settings display semicolons instead.

Basic Exact-Match Example

Suppose a small staff list has employee IDs in A2:A5 and names in B2:B5. Cell E2 contains the ID to find, 1043. With that layout, enter this formula in F2:

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

The result is Mina Patel. Excel checks the IDs for 1043 and returns the value from the same relative position in the names range. If E2 contains an ID absent from A2:A5, the formula displays Not found instead of the default #N/A error.

The fourth argument is optional, but a clear message can make a worksheet easier to use. For example, a customer-facing report might use Not found, while a formula intended for further calculations might use an empty string. An empty string is written as two quotation marks with nothing between them. Use it thoughtfully: a blank-looking result can make missing data less noticeable.

Return Multiple Columns or Search in Either Direction

XLOOKUP can return more than one column from the matching row. Imagine employee IDs in A2:A5, names in B2:B5, departments in C2:C5, and office locations in D2:D5. If F2 contains an ID, put the following formula in G2:

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

For ID 1043, assume the row contains Mina Patel, Operations, and Seattle. The formula returns those three values across G2:I2. This is a dynamic array result: the cells to the right must be empty, or Excel may show a #SPILL! error. The formula needs to be entered only in the first output cell.

XLOOKUP can also return a value to the left of the lookup range. Suppose employee names are in A2:A5, IDs in B2:B5, and E2 contains Mina Patel. With that layout, use:

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

The result is 1043. This works because the lookup and return ranges are specified separately; XLOOKUP does not impose VLOOKUP’s left-to-right column restriction.

Find the Last Match or a Partial Match

When a list contains repeated items, the default search returns the first match. To find the most recent occurrence as represented by the last row, use the search-mode argument to search from bottom to top. For example, with product names in A2:A6, prices in B2:B6, and a product name in E2, enter:

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

If the product in E2 appears more than once and its last listed price is 18.5, the result is 18.5. The 0 requests an exact match; the -1 tells XLOOKUP to search from the last item upward. This is useful for transaction logs where the latest entry is stored at the bottom, but it assumes row order represents recency.

For partial text searches, XLOOKUP supports wildcard matching when its match-mode argument is 2. Suppose A2:A5 contains Northwind Paper, Northwind Office, Southwind Paper, and Southwind Office; B2:B5 contains their account codes; and E2 contains Northwind*. With that layout, use:

=XLOOKUP(E2,A2:A5,B2:B5,"No match",2)

The formula returns the code for Northwind Paper, the first matching entry. The asterisk represents any sequence of characters; a question mark represents one character, and a tilde can escape a wildcard character. If several records fit the pattern, the first match may not be the one you intend, so use a more specific search term or a different method to identify the right record.

Approximate Match for Score Bands

XLOOKUP can match a value to the next smaller or next larger item. This is useful for thresholds such as grade bands, shipping tiers, or commission brackets. Put minimum scores 0, 50, 75, and 90 in ascending order in A2:A5, and the corresponding grades F, C, B, and A in B2:B5. If E2 contains a score of 82, use:

=XLOOKUP(E2,A2:A5,B2:B5,"Below range",-1)

The result is B, because 75 is the largest threshold that does not exceed 82. Match mode -1 means exact match or next smaller item. A score of 90 returns A, while a score below 0 returns Below range. The thresholds should be in ascending order for this example’s logic to be easy to verify; do not confuse the match-mode argument with the separate search-mode argument.

For a nearest threshold above the lookup value, match mode 1 means exact match or next larger item. Approximate matching is not the same as choosing the mathematically nearest value on either side. Define the thresholds and boundaries for the task, then test values at each boundary, just below it, and just above it.

XLOOKUP Compared With VLOOKUP and INDEX/MATCH

VLOOKUP remains familiar and may be needed when a workbook must support older Excel versions, but it searches only in the first column of its table and normally returns a column to the right. Its column number can also become incorrect if someone inserts or rearranges columns. XLOOKUP identifies the lookup range and return range separately, which makes the intention more apparent and allows a leftward return.

INDEX and MATCH can perform flexible lookups and remain a useful option in older Excel releases. However, the two-function pattern can be less readable to people who are new to spreadsheets. XLOOKUP also offers a built-in missing-value argument and can return multiple columns in supported versions. Choosing between them is often a compatibility decision: if recipients use Excel 2016 or 2019, consider a formula supported by their version or confirm that they can upgrade before sharing an XLOOKUP-based workbook.

HLOOKUP is designed to search across a horizontal row and return a value from a row below it. XLOOKUP can handle horizontal ranges too: use a row as the lookup range and the corresponding row as the return range. That means you do not usually need to choose a different lookup function merely because the data is arranged horizontally.

Common Errors and Ways to Fix Them

#N/A: No exact match was found, or the lookup value differs from the stored value. Check spaces, spelling, and whether one value is stored as text while the other is numeric. Add a meaningful fourth argument if a custom missing-value message is appropriate.
#SPILL!: A multi-cell return cannot fill its output area. Clear any values or merged cells blocking the spill range, then check that the formula is in a suitable location.
#VALUE!: The lookup and return arrays have incompatible dimensions. For a vertical search, make sure the ranges have matching heights; for a horizontal search, make sure they have matching widths.
#NAME?: The Excel version may not support XLOOKUP, or the function name may be misspelled. Confirm the recipient’s version if the formula is being shared.

Duplicates are another common source of unexpected results. With the normal search mode, XLOOKUP returns the first matching item, not every item. Use reverse search to return the last match, or use a function designed to filter and return multiple matching records if that is what the task requires. Also check that lookup and return ranges begin and end on corresponding rows; matching the wrong-sized or offset ranges can produce a plausible but incorrect answer.

Build a Reliable Lookup

Before writing the formula, decide what constitutes a match and what should happen when no match exists. Then confirm that the lookup range and return range refer to corresponding records. For reusable workbooks, tables and clear column headings can help keep source data organized as rows are added. If the formula will be copied, consider whether its references should move or remain fixed, and use absolute references where needed.

A quick validation routine helps catch silent mistakes. Test a value known to exist, one known not to exist, and—if duplicates or thresholds are involved—an example that exercises that special case. Compare the returned value with the source row rather than judging only by whether the cell displays an error. For a score band, test the exact cutoff and the values immediately below and above it.

Frequently Asked Questions

Does XLOOKUP require an exact match?
Exact match is the default, so you do not need to specify a match mode for a standard exact lookup. You can choose next-smaller, next-larger, or wildcard matching by setting the optional match-mode argument.

Can XLOOKUP return a value to the left?
Yes. Set the lookup range and return range independently. The return range can be located on either side of the lookup range, provided the ranges correspond to the same records.

How do I return the last matching result?
Set the search-mode argument to -1 to search from the last item to the first. If repeated items represent dated entries, ensure the rows are ordered so that the final occurrence really is the one you want.

Why does XLOOKUP show #N/A?
It usually means no match was found. Check for extra spaces, different data types, or a value that is not present in the lookup range. You can supply a custom not-found result as the fourth argument, but that does not correct an incorrect lookup range.

Does XLOOKUP work in older Excel versions?
XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not as a built-in function in Excel 2019 or Excel 2016. For a workbook shared with users on older releases, use a compatible alternative or confirm version support first.

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.