XLOOKUP Formula: Real Examples in Excel

The basic XLOOKUP formula is =XLOOKUP(E2,A2:A6,B2:B6): it looks for the value in E2 within A2:A6 and returns the corresponding value from B2:B6. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, and it can search in either direction, return a custom message when nothing matches, and handle approximate matches.

What XLOOKUP does

XLOOKUP finds a value in one range and returns a related value from another range. For example, you can look up an employee ID in a list and return that employee’s department. Unlike older lookup methods, the lookup range and the result range do not have to be arranged with the lookup range on the left. XLOOKUP can also search from the bottom of a list upward.

Its main arguments are the value to find, the range to search, and the range containing the result. Optional arguments let you specify what to show when a value is missing, choose exact or approximate matching, and set the search direction. In ordinary use, XLOOKUP looks for an exact match and searches from the first item to the last, so you usually need to provide only the first three arguments.

The lookup and return ranges should correspond: if the lookup range has six rows, the return range should have six rows. They can be vertical columns or horizontal rows. If their dimensions do not align, Excel may return an error or an unexpected result.

Find a matching value

Suppose employee IDs are in A2:A6, departments are in B2:B6, and the ID to find is entered in E2. With IDs 104, 108, 112, 116, and 120 matched row by row to Sales, Finance, Support, Operations, and Marketing, the formula is:

=XLOOKUP(E2,A2:A6,B2:B6)

If E2 contains 112, the result is Support. XLOOKUP compares E2 with each ID in A2:A6, then returns the department from the corresponding position in B2:B6. The ranges do not need to include their heading cells; excluding headings helps avoid accidentally matching a label.

Exact matching is the default. That matters because many readers assume that a lookup function automatically finds the closest value. XLOOKUP does not do that unless you explicitly select an approximate match mode.

Show a useful message when no match exists

A missing value normally produces the #N/A error. You can replace that error with a clear message by supplying the optional “if not found” argument. Assume product codes are in A2:A6, product names are in B2:B6, and the requested code is in E2:

=XLOOKUP(E2,A2:A6,B2:B6,"Code not found")

If E2 contains a code that is not listed, the result is Code not found. If it does match, the formula returns the product name. This is often more helpful in a report than displaying #N/A, especially when a missing record is an expected possibility rather than a spreadsheet mistake.

The message only handles the case where the lookup value is not found. It does not conceal every possible error in the formula. For example, mismatched range dimensions or an invalid reference may still need to be corrected rather than hidden behind a generic message.

Return more than one column

XLOOKUP can return a range of columns for the matching row. Suppose product codes are in A2:A6, product names are in B2:B6, prices are in C2:C6, and the code to find is in E2. To return both the name and price, use:

=XLOOKUP(E2,A2:A6,B2:C6,"Code not found")

If the matching row contains “Desk lamp” in column B and 24.5 in column C, Excel returns Desk lamp and 24.5 in adjacent cells. In current Excel versions with dynamic arrays, the results spill into the cells to the right. Those cells must be empty; otherwise Excel displays a #SPILL! error. Clear the obstructing cells or move the formula to an area with enough room.

This is useful when a report needs several attributes for one key. It can also make formulas easier to maintain than separate lookups for each returned field. The returned columns must still line up row by row with the lookup range.

Look from right to left or find the latest match

VLOOKUP traditionally requires the lookup column to be the leftmost column in its table. XLOOKUP has no such restriction. If product names are in A2:A6, product codes are in B2:B6, and the name to search is in E2, this formula returns the code:

=XLOOKUP(E2,A2:A6,B2:B6)

Here the result range is to the right, but the same arrangement works if the result column is to the left: simply make the first range the column being searched and the second range the one to return. The order of the columns on the worksheet does not dictate which direction XLOOKUP can use.

The optional search mode can also make XLOOKUP search from the last item toward the first. Assume transaction IDs are in A2:A8, statuses are in B2:B8, and the ID to find is in E2. If the same ID can appear more than once and you want the last listed status, use:

=XLOOKUP(E2,A2:A8,B2:B8,"No transaction",0,-1)

The final -1 tells XLOOKUP to search in reverse order. If that ID appears on rows 3 and 7, the formula returns the status from row 7. This finds the last matching row in the list; it does not automatically determine which date is newest. The list must be arranged so that its last relevant record is the one you intend to return.

Use an approximate match for bands and thresholds

Approximate matching can assign a value to a category such as a grade band, shipping bracket, or commission tier. Assume minimum score thresholds are in A2:A6, corresponding grades are in B2:B6, and a student’s score is in E2. The thresholds are 0, 60, 70, 80, and 90, with grades F, D, C, B, and A. Use:

=XLOOKUP(E2,A2:A6,B2:B6,"Below range",-1)

With a score of 84, the result is B: match mode -1 means exact match or the next smaller item, so 80 is the applicable threshold. A score of 90 returns A, while a score below 0 returns Below range. If you instead used match mode 1, a score of 84 would match the next larger threshold, 90, which is not the intended rule in this example.

For approximate matches, write down the rule in plain language before choosing a mode: should a value use the next lower threshold or the next higher one? XLOOKUP’s ordinary search can find approximate matches in an unsorted range, but keeping threshold tables sorted in the direction readers expect makes them easier to inspect and reduces accidental errors. The binary search modes are a separate option and require sorted data; do not select them unless the range is correctly ordered.

Find text containing a word

Wildcard matching is useful when you know only part of a text value. Assume full names are in A2:A6, phone extensions are in B2:B6, and you want the first name containing “North.” Enter this formula:

=XLOOKUP("*North*",A2:A6,B2:B6,"No match",2)

The 2 selects wildcard matching. The asterisk represents any number of characters, so the pattern can match text before and after North. If the first matching name is “Northside Office,” the formula returns the extension from that row. Because the search proceeds from the top by default, the first matching row wins if several names contain the text.

Wildcards are for text matching, not a substitute for exact numeric comparisons. The question mark wildcard represents one character, while a tilde can be used to escape a wildcard character when you need to search for it literally. If the workbook’s data has inconsistent spaces or spelling, a wildcard can help with a partial search, but cleaning the source text may be a better long-term fix.

Combine XLOOKUPs for a two-way lookup

A two-way lookup chooses both a row and a column, such as finding a department’s sales for a particular quarter. Assume department names are in A2:A5, quarter headings are in B1:D1, sales values are in B2:D5, the requested department is in F2, and the requested quarter is in G1:

=XLOOKUP(F2,A2:A5,XLOOKUP(G1,B1:D1,B2:D5))

The inner XLOOKUP finds the quarter heading and returns the corresponding sales column. The outer XLOOKUP then finds the department in that returned column. If F2 is Support and G1 is Q2, the result is the value at the intersection of Support and Q2. Check that the quarter labels and department names match the table exactly; an extra space or different label can prevent a match.

This nested approach is convenient when the table layout is clear and both headings are unique. For a larger workbook, give the source ranges descriptive names or use Excel tables where appropriate, so it is easier to see which labels and figures each part of the formula refers to.

Common XLOOKUP problems and fixes

#N/A: The searched value was not found. Check for misspellings, extra spaces, different data types, or a value outside the selected range. Add an “if not found” message if a missing result is normal.
#SPILL!: A formula returning multiple cells cannot place its results because one or more destination cells are occupied. Clear those cells or move the formula.
Unexpected result: Confirm that the formula searches the intended range and that duplicate values are handled as intended. By default, XLOOKUP returns the first match from the top.
Wrong approximate result: Confirm whether you need the next smaller or next larger value. In the threshold example, -1 is the next-smaller option.
Formula not recognized: XLOOKUP is not available in older Excel versions such as Excel 2019 and Excel 2016. Update to Microsoft 365 or use an alternative lookup method.

Also check how the key is stored. A number stored as text may not match the same-looking numeric value, and leading or trailing spaces can make text values differ. Avoid “fixing” a mismatch by changing the formula until you have confirmed what is actually in the source cells.

XLOOKUP compared with VLOOKUP and INDEX/MATCH

For a new lookup formula, XLOOKUP is often more direct than VLOOKUP because it takes separate lookup and return ranges and can return values from either side. It also has built-in arguments for missing values and reverse searching. VLOOKUP remains useful when maintaining older workbooks or sharing files with people whose Excel version does not support XLOOKUP.

INDEX/MATCH is another established approach. It can look in different directions and works in older Excel versions, but it generally requires composing more than one function. Choosing XLOOKUP does not require converting every old formula: compatibility with the workbook’s users and the time needed to maintain it are practical considerations. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; older releases may show the function as unavailable.

In Excel for Microsoft 365 on Windows, enter the formula in a cell and press Enter. The same XLOOKUP formula is supported in current Microsoft 365 versions for Mac and Excel for the web. Depending on regional settings, Excel may use semicolons rather than commas as argument separators; if a comma-separated example is rejected, check the list separator configured for that installation.

Frequently Asked Questions

Is XLOOKUP exact match by default?
Yes. If you omit the match-mode argument, XLOOKUP looks for an exact match. Use an explicit match mode when you need an approximate match or wildcard search.

Can XLOOKUP return multiple columns?
Yes. Set the return range to include the desired columns. In Microsoft 365 and other dynamic-array versions, the returned values spill into neighboring cells, which must be clear.

Why does my XLOOKUP return #N/A?
Usually, the lookup value does not exactly match anything in the search range. Check spelling, spaces, number-versus-text differences, and whether the correct rows are included. You can also provide a custom not-found message.

Can XLOOKUP search from the bottom?
Yes. Set its search-mode argument to -1 to search from last to first. This returns the last matching entry in the selected range, not necessarily the chronologically latest record unless the data is ordered accordingly.

Does XLOOKUP work in Excel 2019?
No. XLOOKUP is supported in Microsoft 365 and Excel 2021 or later, but not Excel 2019 or Excel 2016. Use a compatible alternative such as INDEX/MATCH or VLOOKUP when a workbook must work in those older releases.

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.