XLOOKUP Function Examples: Exact, Approximate, and More

XLOOKUP searches one range for a value and returns the corresponding value from another range. For example, with product codes in A2:A6, quantities in B2:B6, and the code to find in E2, enter =XLOOKUP(E2,A2:A6,B2:B6,"Not found") to return its quantity or display Not found if the code is absent.

The examples below are for Microsoft Excel for Microsoft 365 on Windows. XLOOKUP is also available in Excel 2021 and later, including Excel for Mac and Excel for the web; it is not available in Excel 2019 or Excel 2016. The same basic formulas work across supported versions, though menus and some interface details can differ.

How XLOOKUP works

XLOOKUP looks through a lookup array for a lookup value, then returns the item at the corresponding position in a return array. The general syntax is =XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode]). The square brackets in this syntax mark optional arguments; do not type them into a formula unless they are part of an array reference.

lookup_value: The value to search for, such as a product code, name, date, or cell reference.
lookup_array: The one row or one column in which Excel searches.
return_array: The row, column, or compatible range containing the results.
if_not_found: Optional text or a value to return when there is no match.
match_mode: Optional setting for exact, approximate, or wildcard matching.
search_mode: Optional setting for search direction or binary search of sorted data.

By default, XLOOKUP searches for an exact match, from the first item to the last. Unlike older lookup formulas, the lookup column does not have to be at the left of the results column, and the data does not need to be sorted for an ordinary exact-match search.

Example: Find a value by an exact match

Suppose A2:A6 contains the codes P-101, P-102, P-103, P-104, and P-105, while B2:B6 contains their stock counts: 12, 7, 25, 18, and 4. If E2 contains P-104, the formula below returns 18.

With product codes in A2:A6, stock counts in B2:B6, and the requested code in E2, use =XLOOKUP(E2,A2:A6,B2:B6,"Not found"). The result is 18 when E2 is P-104; if E2 is not in the code list, the formula displays Not found.

The fourth argument is optional, but it is useful in worksheets people rely on. If you leave it out and Excel cannot find an exact match, XLOOKUP returns the #N/A error. A clear fallback such as Not found can make a report easier to read and help distinguish a missing record from a broken formula.

Example: Look to the left

XLOOKUP can return a value from a column to the left of the lookup column. This is useful when a table’s layout places the search field after the information you want to retrieve. For instance, A2:A5 could hold employee names and B2:B5 their email addresses.

With names in A2:A5, email addresses in B2:B5, and the email to find in E2, enter =XLOOKUP(E2,B2:B5,A2:A5,"No match"). If E2 contains the email address listed beside Jordan Lee, the result is Jordan Lee. The lookup array is B2:B5 and the return array is A2:A5; their left-to-right order does not matter.

This is a common advantage over VLOOKUP, whose usual structure requires the lookup field to be in the first column of the table array and returns values to its right. INDEX and MATCH can also look left, but XLOOKUP expresses the search and return ranges directly in one function.

Example: Return a result for multiple criteria

To match more than one condition, multiply logical tests that produce TRUE or FALSE values. In Excel arithmetic, TRUE and FALSE act like 1 and 0, so a row where every condition is true produces 1. This method is useful when a product name can appear under several departments.

With departments in A2:A10, product names in B2:B10, prices in C2:C10, the department to match in E2, and the product in F2, use =XLOOKUP(1,(A2:A10=E2)*(B2:B10=F2),C2:C10,"No match"). If the matching department-product row has a price of 24.50, the result is 24.50. If there is no row that satisfies both conditions, the result is No match.

If several rows satisfy the conditions, the default search direction returns the first matching row. Check whether that is the intended result; if you need the last qualifying row, see the reverse-search example below. Keep the condition ranges the same size as the return range so each test corresponds to the right row.

Example: Return more than one column

XLOOKUP can return a range of results rather than just one cell. In current Microsoft 365, Excel 2021, and Excel 2024, the results can spill into neighboring cells automatically. Make sure those destination cells are empty, or Excel may show a #SPILL! error.

With product IDs in A2:A5, product names, prices, and stock counts in B2:D5, and the requested ID in F2, enter =XLOOKUP(F2,A2:A5,B2:D5,"Not found"). If the matching row contains Desk lamp, 32, and 14 in columns B through D, the formula returns those three values in adjacent cells. This saves writing separate lookup formulas for each returned field.

The result can spill horizontally or vertically according to the shape of the return array. If the formula is copied into a location where any intended output cell is occupied, clear the obstructing cells or move the formula. A merged cell in the spill area can also prevent the result from appearing.

Example: Find an approximate match

For numeric breakpoints, set match_mode to -1 to find an exact match or the next smaller value. For example, A2:A5 might contain the ascending thresholds 0, 50, 100, and 200, while B2:B5 contains Basic, Standard, Plus, and Premium.

With thresholds in A2:A5, tier names in B2:B5, and a score in E2, enter =XLOOKUP(E2,A2:A5,B2:B5,"No bracket",-1). If E2 is 135, the formula returns Plus because 100 is the largest threshold not greater than 135. If E2 is below the smallest threshold, it returns No bracket.

Use match_mode 1 to find an exact match or the next larger value. Approximate matching is different from the default exact search, so state the intended rule clearly. For threshold examples, ascending breakpoints make the logic easy to inspect; test values at the breakpoints themselves and just above or below them to confirm the categories are assigned as intended.

Example: Use wildcards or search from the end

To find text that contains a pattern, set match_mode to 2. The asterisk wildcard represents any number of characters, the question mark represents one character, and a tilde escapes a wildcard when you want to match it as a literal character.

With descriptions in A2:A10, matching return values in B2:B10, and the pattern *north* in E2, use =XLOOKUP(E2,A2:A10,B2:B10,"No match",2). This returns the value from the first description containing north. Because the asterisk can match zero or more characters, the pattern can match text with other words before or after north.

To return the last matching entry instead of the first, set search_mode to -1. With IDs in A2:A20, status values in C2:C20, and the requested ID in E2, use =XLOOKUP(E2,A2:A20,C2:C20,"No match",0,-1). This returns the status from the last row containing that ID. It returns the latest record only if the rows are arranged so the latest record appears last; XLOOKUP does not determine which date is newest unless the data order supports that conclusion.

Choosing match and search settings

For most everyday lookups, keep the defaults: exact matching and a first-to-last search. Choose a different setting only when the data and the question call for it. The optional arguments appear in a fixed order, so to specify search_mode you must also provide the earlier optional arguments.

Exact match: Leave match_mode blank or use 0. This is usually right for IDs, codes, and names.
Next smaller: Use -1 for an exact match or the next smaller item, often with numeric thresholds.
Next larger: Use 1 for an exact match or the next larger item.
Wildcard: Use 2 for patterns containing * or ?, with ~ to escape wildcard characters.
Last-to-first: Use search_mode -1 to find the last matching entry in the range.

Search modes 2 and -2 use binary search and require the lookup array to be sorted in the appropriate order. They can be useful with very large, correctly sorted ranges, but an incorrectly sorted array can produce invalid results. For ordinary workbooks, the default linear search is simpler and avoids that sorting requirement.

Common XLOOKUP problems and fixes

A formula that looks right can still fail when the underlying values differ. Before changing the formula, check the source data and confirm that the lookup and return ranges refer to corresponding rows or columns.

#N/A: No exact match was found, or the fallback argument was omitted. Check for misspellings, extra spaces, and text-versus-number differences; add an if_not_found value if a readable message is appropriate.
#SPILL!: A multi-cell result cannot expand because a cell in the output area is occupied or otherwise blocks the spill. Clear the area or move the formula.
Unexpected result: Duplicate lookup values return the first match by default. Use reverse search if the last occurrence is the one you need, and verify the row order.
Wrong approximate result: Check match_mode, the ordering and meaning of your breakpoints, and boundary cases. Approximate matching is not the same as finding the closest value in either direction.
Formula entry issue: Make sure the lookup and return ranges have compatible dimensions. In some regional settings, Excel expects semicolons instead of commas as argument separators; use the separator configured for that installation if Excel rejects a pasted formula.

When data may contain stray spaces, cleaning the source values is often better than building a complicated lookup formula. Also check whether a number stored as text is being compared with a true numeric value. Excel can treat those as different lookup values even when they look alike on the worksheet.

XLOOKUP compared with VLOOKUP and INDEX/MATCH

XLOOKUP is often easier to maintain than VLOOKUP because it names the lookup array and return array separately. Inserting a column into a table is less likely to make the formula point at the wrong return-column number, and the lookup range can be to the right or left of the result range. XLOOKUP also supports an explicit not-found result and can return multiple columns.

VLOOKUP remains useful when sharing files with people using older Excel versions that do not support XLOOKUP. INDEX and MATCH is another flexible option for older workbooks and can search in either direction, though it typically uses a nested formula. A workbook that uses XLOOKUP may display a compatibility problem or an unsupported-function error for someone opening it in Excel 2019 or 2016, so consider the recipients’ software before choosing a formula.

Frequently Asked Questions

Is XLOOKUP exact match by default?
Yes. If you omit match_mode, XLOOKUP searches for an exact match. If no match is found, it returns #N/A unless you supply the optional if_not_found argument.

Can XLOOKUP return a value to the left?
Yes. Set lookup_array to the column containing the value to search for and return_array to the column containing the answer. The return range does not have to be to the right.

Why does XLOOKUP return #N/A when the value appears to be there?
The stored values may not be identical. Check for leading or trailing spaces, different punctuation, hidden characters, or a number stored as text in one range and as a number in the other.

Can XLOOKUP return multiple columns?
Yes. Use a multi-column return_array, such as B2:D5, and Excel can spill the matching row of results into adjacent cells. The spill range must be clear.

Does XLOOKUP work in Excel 2019?
No. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including Excel 2024, but not Excel 2019 or Excel 2016. Use a compatible alternative such as VLOOKUP or INDEX/MATCH when you need to support those versions.

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.