XLOOKUP Syntax Examples for Excel

For an exact lookup, with the value to find in E2, lookup keys in A2:A100, and results in B2:B100, use =XLOOKUP(E2,A2:A100,B2:B100). XLOOKUP searches the key range and returns the corresponding value from the result range; the sections below explain the full syntax and show practical variations.

XLOOKUP syntax and arguments

XLOOKUP has six arguments, in this order: lookup_value, lookup_array, return_array, [if_not_found], [match_mode], and [search_mode]. The last three are optional, so a basic formula can use only the first three. In Excel, commas separate arguments in the examples here; some regional settings display a different list separator.

lookup_value is the value, cell, or expression Excel should find.
lookup_array is the single row or column to search.
return_array is the corresponding row or column from which Excel returns a result.
if_not_found is an optional result to show when there is no match.
match_mode controls exact, approximate, or wildcard matching.
search_mode controls search direction and, optionally, binary searching.

Unlike VLOOKUP, XLOOKUP does not require the lookup column to be the leftmost column of a table, and its lookup and return ranges do not have to be adjacent. The lookup and return arrays should, however, have compatible dimensions: a vertical lookup range should return from a corresponding vertical range, and a horizontal lookup range from a corresponding horizontal range.

Basic exact-match example

Suppose column A contains product codes, column B contains product names, and E2 contains the code to look up. With codes in A2:A100 and names in B2:B100, use:

=XLOOKUP(E2,A2:A100,B2:B100)

By default, XLOOKUP looks for an exact match. If E2 contains a code found in A2:A100, Excel returns the name from the same relative position in B2:B100. For example, if the code in E2 is in A7, the formula returns B7. If there is no match and you have not supplied an alternative, Excel returns the #N/A error.

The ranges can also be full columns, such as A:A and B:B, but bounded ranges or Excel Tables are often easier to audit and can avoid calculating over more cells than necessary. In a table, structured references can make the formula easier to read and expand automatically when rows are added.

Choose a result when no match is found

To show a useful message instead of #N/A, add the fourth argument. With the same product-code layout—lookup value in E2, keys in A2:A100, and names in B2:B100—the formula is:

=XLOOKUP(E2,A2:A100,B2:B100,"Code not found")

If E2 is absent from the key range, the result is Code not found. This optional argument handles the no-match case; it does not suppress every possible error. For example, if a matching cell in the return range itself contains an error, that error can still appear. Avoid replacing every error with a generic message if you need to distinguish a missing key from a problem in the source data.

Approximate matches and wildcard searches

The fifth argument, match_mode, changes what counts as a match. Its default is 0, meaning exact match. Use -1 for an exact match or the next smaller item, 1 for an exact match or the next larger item, and 2 for wildcard matching. Approximate matching is useful for bands such as commission tiers or grade thresholds; make sure the table’s meaning and ordering are clear before relying on it.

For example, suppose A2:A5 contains the ascending lower limits 0, 50, 70, and 90, and B2:B5 contains the corresponding grades F, D, C, and A. With a score in E2, this formula returns the grade for the exact threshold or the next smaller threshold:

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

If E2 is 83, the formula returns C because 70 is the greatest listed threshold below 83. If E2 is below the smallest threshold and there is no exact match, the formula returns No grade. This is a threshold lookup, not an interpolation: it does not calculate a value between two thresholds.

For wildcard matching, use match mode 2. With search text in E2, names in A2:A100, and IDs in B2:B100, this formula returns the ID for the first name matching the pattern:

=XLOOKUP("*"&E2&"*",A2:A100,B2:B100,"No match",2)

The asterisk matches any number of characters, while a question mark matches one character. To search for a literal asterisk or question mark, precede it with a tilde. Wildcards can be helpful for partial text, but a broad pattern may match more than one entry; XLOOKUP returns one result according to its search order.

Search from the bottom or use binary search

The sixth argument, search_mode, is 1 by default, which searches from the first item to the last. Set it to -1 to search from the last item to the first. This is useful when records are ordered by time and you want the latest matching entry. With dates in A2:A100, amounts in B2:B100, and the date to find in E2, use:

=XLOOKUP(E2,A2:A100,B2:B100,"No record",0,-1)

This searches upward from the end of the ranges and returns the amount for the last exact match. If duplicate dates appear, the last matching row wins. Check that the row order represents the meaning of “latest”; a reverse search finds the last row, not necessarily the most recent date if the data is unsorted.

Search modes 2 and -2 request binary search in ascending and descending order, respectively. Binary search can be faster on large sorted ranges, but it depends on the lookup array being sorted in the required order. An unsorted range can produce an incorrect result. For ordinary worksheets, the default linear search is usually the safer choice unless the data order is controlled.

Return values from another direction or multiple columns

XLOOKUP can look to the left of its key column. With IDs in C2:C100, names in A2:A100, and the ID to find in E2, use:

=XLOOKUP(E2,C2:C100,A2:A100,"ID not found")

The formula searches column C and returns from column A. There is no need to rearrange the source table or count a column index, which is one reason XLOOKUP is more flexible than VLOOKUP.

XLOOKUP can also return several adjacent columns at once. With IDs in A2:A100, names and departments in B2:C100, and the ID in E2, use:

=XLOOKUP(E2,A2:A100,B2:C100,"ID not found")

In Excel versions with dynamic arrays, the matching name and department spill into neighboring cells. Keep those destination cells empty; a value blocking the spill area causes a #SPILL! error. If you need only one field, point the return array to that single column instead.

Two-way lookups and multiple conditions

For a two-way lookup, one XLOOKUP can find a row and another can select a column from that row. Assume product names are in A2:A20, month headings are in B1:G1, sales data is in B2:G20, the product to find is in J2, and the month is in J3. Use:

=XLOOKUP(J2,A2:A20,XLOOKUP(J3,B1:G1,B2:G20))

The inner XLOOKUP selects the column for the month; the outer one finds the product within that selected column. The formula returns the sales figure where that product’s row and month’s column intersect. Check that the month heading in J3 matches a heading in B1:G1 and that product names are consistent.

When a record is defined by two fields together, such as department and employee, you can combine those fields for the lookup. With departments in A2:A100, employees in B2:B100, return values in C2:C100, and criteria in H2 and I2, use:

=XLOOKUP(H2&I2,A2:A100&B2:B100,C2:C100,"No match")

This checks for both criteria in the same row and returns the associated value. Choose fields that together identify a unique record; if multiple rows meet the combined criteria, XLOOKUP returns the first match by default. For larger or more complex datasets, a helper column containing a carefully constructed key can be easier to inspect and maintain.

Common XLOOKUP problems

Unexpected #N/A: Check for extra spaces, different spellings, or a text-versus-number mismatch. A number stored as text may look identical to a numeric key but not compare as expected.
Wrong duplicate returned: The default search starts at the beginning. Use search mode -1 if the intended result is the last matching row, and verify the data order.
Wrong approximate result: Confirm the match mode and the meaning of each threshold. If you use binary search, the lookup array must be sorted in the specified direction.
#VALUE! or unexpected results: Confirm the lookup and return arrays have corresponding row or column dimensions and that the lookup range contains the intended keys.
#SPILL!: For a formula returning multiple cells, clear any occupied cells in the spill area.
Formula not recognized: XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. It is not available in Excel 2019 or Excel 2016. Excel for the web and supported Mac versions also provide XLOOKUP; the formulas are generally the same, though menus and interface details can vary.

For reliable results, test a known key, a missing key, and any duplicate-key case. If the formula is used for reporting, document whether duplicates should return the first or last record and whether approximate matching is intentional.

XLOOKUP compared with VLOOKUP and INDEX/MATCH

VLOOKUP searches the first column of a table and returns a value to its right, using a column number in the formula. Inserting or rearranging columns can make that number fragile, and VLOOKUP cannot directly return a column to the left. XLOOKUP specifies the lookup and return ranges separately, supports leftward returns, and defaults to exact matching.

INDEX/MATCH can also perform flexible lookups, including leftward ones, and remains useful in older Excel versions that do not have XLOOKUP. It often requires a nested formula, while XLOOKUP puts the key range and result range together in one function call. Choose based on workbook compatibility: a file shared with people using Excel 2016 or 2019 may need an older method, while a workbook limited to Microsoft 365 or Excel 2021 and later can use XLOOKUP.

Frequently Asked Questions

What is the basic XLOOKUP formula?
Use =XLOOKUP(lookup_value,lookup_array,return_array) for a default exact match. Replace the argument names with a value or cell reference, the range to search, and the corresponding range to return from.

Does XLOOKUP return an exact match by default?
Yes. Its default match mode is exact match, unlike VLOOKUP’s default approximate behavior when the final argument is omitted or TRUE. You can still set the match mode explicitly when a formula’s intent should be especially clear.

How do I make XLOOKUP return blank instead of #N/A?
Add an empty text string as the fourth argument. For example, with a lookup value in E2, keys in A2:A100, and results in B2:B100, use =XLOOKUP(E2,A2:A100,B2:B100,""). This returns a blank-looking result only when no key matches.

Can XLOOKUP return the last match?
Yes. Set the sixth argument, search mode, to -1 to search from the last item to the first. This returns the last matching row in the current range order, so it only represents the latest record if the rows are ordered appropriately.

Why is XLOOKUP unavailable in my Excel?
Older perpetual versions such as Excel 2016 and Excel 2019 do not include XLOOKUP. Use Microsoft 365 or Excel 2021 or later, or use a compatible alternative such as INDEX/MATCH when the workbook must support an older installation.

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.