Excel XLOOKUP Examples: Formulas and Tips

Microsoft Excel’s XLOOKUP finds a value in one range and returns the corresponding value from another; for example, =XLOOKUP(D2,A2:A6,B2:B6,"Not found") looks for the value in D2 within A2:A6 and returns the matching entry from B2:B6. XLOOKUP uses exact matching by default, can search in either direction, and can return a custom message when no match exists.

What XLOOKUP does and where it is available

XLOOKUP is a lookup function for connecting related information in a worksheet. You might use it to find a product’s price from its code, retrieve an employee’s department from an ID, or return a grade from a score. Unlike older lookup approaches, the lookup range and return range do not need to be arranged in a particular left-to-right order.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including supported Excel editions for Windows, Mac, and the web. It is not available in Excel 2019 or Excel 2016. If a workbook containing XLOOKUP is opened in an older edition, that edition may show the unsupported-function error #NAME?. A formula can also be replaced with a value or an older-compatible formula if the file must work in those versions.

The examples below use ordinary cell ranges and comma argument separators, as used in Excel for Microsoft 365 on Windows. A Mac or web user can use the same function and formulas. Depending on regional settings, Excel may use semicolons instead of commas between formula arguments.

How XLOOKUP works

XLOOKUP searches a lookup array for a value, then returns a value from the corresponding position in a return array. The lookup and return arrays should line up: if the lookup array is a column containing six items, the return array should have six corresponding rows. They can be on either side of each other, and the return array can contain multiple columns.

The function has optional settings for what to show when there is no match, whether to use approximate or wildcard matching, and which direction to search. If those settings are omitted, XLOOKUP looks for an exact match and searches from the first item to the last. That default is useful for most ID, name, and code lookups, and it avoids the approximate-match behavior that can surprise users of older lookup formulas.

For an exact lookup, begin with the value you want to find, select the range to search, and select the aligned range containing the answer. Add a not-found message if you want a readable alternative to an error. A message such as “Not found” makes a missing record visible without implying that the closest result is correct.

Example: find a price from a product code

Suppose product codes are in A2:A6 and their prices are in B2:B6. Cell D2 contains the code P-103. With those ranges and that lookup value, enter =XLOOKUP(D2,A2:A6,B2:B6,"Not found"). If P-103 is on the third row of the product list and its price in B4 is 18.50, the formula returns 18.50. If D2 contains a code that is absent from A2:A6, it returns Not found.

The ranges are separate arguments, so the search can be done in one column and the result returned from another. This is one practical difference from VLOOKUP, which normally requires the lookup column to be the leftmost column in its table range. XLOOKUP also uses exact matching by default, so there is no need to add an exact-match setting for this example.

If the result should remain blank when the code is absent, the not-found argument can be an empty text string. With codes in A2:A6, prices in B2:B6, and the requested code in D2, use =XLOOKUP(D2,A2:A6,B2:B6,""). This returns the matching price, or a blank-looking result if there is no match. Use this deliberately: an empty result can be harder to distinguish from a genuinely blank price cell.

Example: return several fields at once

XLOOKUP can return more than one column from the matching row. Assume employee IDs are in A2:A6, names are in B2:B6, departments are in C2:C6, and locations are in D2:D6. If F2 contains the ID to find, the formula =XLOOKUP(F2,A2:A6,B2:D6,"Not found") returns the matching name, department, and location across adjacent cells. For an employee whose row contains “Mina Lee,” “Operations,” and “Denver,” those three values spill into three cells.

Dynamic array results need room to appear. Keep the cells to the right of the formula clear; otherwise Excel may show #SPILL!. If the formula is entered inside an Excel table, spilled results cannot expand within the table in the same way as they do in a normal worksheet area. Place the formula outside the table or return a single field when a spill is not suitable.

Example: find a value using row and column labels

Nested XLOOKUP formulas can locate a value at the intersection of a row and a column. Suppose product names are in A2:A4, month headings are in B1:D1, and the corresponding monthly figures are in B2:D4. Put the product to find in F2 and the month heading in G1. If the rows are Widget, Gadget, and Cable, and the columns are Jan, Feb, and Mar, then =XLOOKUP(F2,A2:A4,XLOOKUP(G1,B1:D1,B2:D4)) returns the figure at that intersection. If Widget’s February figure is 15 and F2 is Widget while G1 is Feb, the result is 15.

The inner lookup finds the requested month and returns that month’s column from the data area. The outer lookup then finds the requested product within that returned column. Check that the labels match exactly, including spaces; a label copied from another source can contain an extra space and fail to match even if it looks identical.

Example: approximate matches for score bands

Approximate matching is useful when a value belongs to a threshold band, such as a letter-grade scale. Suppose the minimum scores are in ascending order in D2:D6: 0, 60, 70, 80, and 90. The corresponding grades are in E2:E6: F, D, C, B, and A. With a student’s score in G2, use =XLOOKUP(G2,D2:D6,E2:E6,"No grade",-1). For a score of 84, it returns B because 80 is the largest threshold less than or equal to 84.

In this formula, match mode -1 means exact match or the next smaller item. Match mode 1 means exact match or the next larger item. These options are not interchangeable: choose the direction that fits the meaning of your thresholds. For the score-band example, thresholds should be sorted from smallest to largest, and the first threshold should cover the lowest possible score. A score below the first threshold produces the not-found result.

Approximate matching does not mean “pick any nearby value.” It follows a defined next-smaller or next-larger rule. If a threshold list is incomplete, unsorted, or contains duplicate boundaries, the result may not reflect the intended policy. Review the boundary values and test scores just below, exactly at, and just above each threshold.

Example: wildcard search and the last matching record

For a text lookup where only part of the name is known, use wildcard match mode. Suppose full names are in A2:A6, departments are in B2:B6, and F2 contains the text Lee. With those ranges, =XLOOKUP("*"&F2&"*",A2:A6,B2:B6,"No match",2) returns the department for the first name containing Lee. Match mode 2 enables wildcards: an asterisk represents any number of characters, and a question mark represents one character. If the lookup text itself needs to match a literal asterisk or question mark, precede that character with a tilde.

By default, XLOOKUP returns the first match it encounters. To find the most recent or last matching entry in a list, change the search direction. Suppose transaction IDs are in A2:A10, statuses are in B2:B10, and F2 contains the ID to find. Use =XLOOKUP(F2,A2:A10,B2:B10,"Not found",0,-1). The final argument -1 searches from the last item toward the first, so repeated IDs return the status from the bottommost matching row. This is useful only if the order of the records has the meaning you intend; it does not automatically determine which record has the latest date unless the rows are ordered accordingly.

Example: match more than one condition

When a record is identified by two fields, such as a person and a region, XLOOKUP can search a combined logical test. Suppose names are in A2:A20, regions are in B2:B20, and sales values are in C2:C20. Put the requested name in F2 and the requested region in G2. Enter =XLOOKUP(1,(A2:A20=F2)*(B2:B20=G2),C2:C20,"No match"). If exactly one row matches both criteria, the formula returns that row’s sales value; if no row matches both, it returns No match.

Each comparison creates a series of TRUE or FALSE values, and multiplying the comparisons makes a 1 where both conditions are true and 0 elsewhere. XLOOKUP searches that calculated series for 1. Make sure all three data ranges have the same number of rows. If several rows satisfy both conditions, the default first-to-last search returns the first one; use a reverse search direction if the last match is required.

Common XLOOKUP problems and how to fix them

#N/A: No matching value was found and the formula does not provide a not-found result. Check spelling, spaces, number-versus-text differences, and the selected lookup range. A custom not-found message can make expected missing records clearer.
#NAME?: The Excel version may not support XLOOKUP, or the function name may be misspelled. Confirm that the workbook is open in Microsoft 365 or Excel 2021 or later.
#VALUE! or an unexpected result: Check that the lookup and return arrays have compatible dimensions and cover corresponding records. A one-column lookup array should not be paired with a return range whose rows do not align.
#SPILL!: A returned array needs cells that are clear. Remove blocking values or move the formula to an open area.
A match appears missing: Text may contain leading or trailing spaces, nonprinting characters, or slightly different punctuation. Check the source data before changing the lookup to a broader wildcard or approximate match.

For troubleshooting, test the lookup value by itself and verify that it appears in the intended lookup range. Also confirm that the selected return range starts on the same row or column as the lookup range. A formula can be syntactically valid and still point to misaligned data.

XLOOKUP compared with VLOOKUP and INDEX/MATCH

VLOOKUP remains useful in older workbooks and Excel editions, but its lookup column must be at the left of the table range, and its return column is specified by a number. Inserting or rearranging columns can make that number less transparent. XLOOKUP names the lookup and return ranges separately, supports searches from bottom to top, and provides a built-in not-found argument.

INDEX/MATCH can also look left or right and is widely used in older Excel files. It remains a practical alternative when a workbook must support versions without XLOOKUP. For a new formula in a compatible Excel version, XLOOKUP is often easier to read, but replacing a working legacy formula is not always necessary. Compatibility with the people and software that will open the workbook is a key part of choosing a function.

Frequently Asked Questions

Does XLOOKUP return the first or last match?
By default, it returns the first match, searching from the start of the lookup array. Set the search mode to -1 to search from the end and return the last match.

Is XLOOKUP exact match by default?
Yes. Its default match mode is exact match. You can explicitly choose another match mode for next-smaller, next-larger, or wildcard matching.

Can XLOOKUP search to the left?
Yes. The lookup array and return array are separate arguments, so the return range can be to the left or right of the range being searched.

Why does XLOOKUP show #NAME? in my workbook?
The Excel version may not include XLOOKUP. It is available in Microsoft 365 and Excel 2021 or later, but not Excel 2019 or 2016. Check the version used to open the workbook before rewriting the formula.

Can XLOOKUP return multiple columns?
Yes. Select multiple columns in the return array and Excel can spill the matching row’s values into adjacent cells. Leave enough empty space for the result.

Can XLOOKUP replace VLOOKUP?
For many lookups, yes, and it removes VLOOKUP’s leftmost-column restriction. But XLOOKUP is not available in some older Excel editions, so use a compatible alternative when recipients need to open the workbook there.

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.