XLOOKUP Entry Examples: Formulas for Common Searches

XLOOKUP entry examples show how to find a value in one range and return its matching value from another. In Microsoft Excel, a basic exact-match formula is =XLOOKUP(D2,A2:A10,B2:B10,"Not found"): it searches for the value in D2 within A2:A10 and returns the corresponding entry from B2:B10, or displays “Not found” if there is no match.

What XLOOKUP does

XLOOKUP is Excel’s modern lookup function. It can search a row or column and return a corresponding result from a different row or column. Unlike older VLOOKUP formulas, it does not require the search column to be the leftmost column of a table, and it can return results from either side of the search range. It also uses an exact match by default, which avoids a common source of unexpected results with older lookup methods.

The function is available in Microsoft 365 and Excel 2021 or later. A workbook containing XLOOKUP may show a #NAME? error in an older Excel version that does not support the function. The same XLOOKUP formulas generally work in current Excel for Mac and Excel for the web; menu names and some keyboard shortcuts can differ from Windows.

To enter a formula, select the cell where you want the answer, type an equals sign and the function, then fill in its arguments. You can type directly in the cell or in the formula bar. Excel’s formula autocomplete can suggest XLOOKUP as you type. Press Enter on Windows to commit the formula; on Mac, press Return. If Excel rejects commas between arguments, your regional settings may use semicolons as the list separator.

XLOOKUP syntax and arguments

The full syntax is =XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode]). The first three arguments are required. The remaining arguments are optional and control what happens when there is no match, what counts as a match, and the direction or method of searching.

lookup_value: The value to find, such as a product code, name, or cell reference.
lookup_array: The single row or column in which Excel searches.
return_array: The row or column containing the result to return. It must align in size with the lookup array.
if_not_found: Optional text or another value to return when no match exists. Without it, XLOOKUP returns #N/A.
match_mode: Optional setting for exact, approximate, or wildcard matching.
search_mode: Optional setting for search direction or binary search.

Square brackets in the syntax indicate optional arguments; do not type the brackets into a formula. If you want to set a later option, include the earlier optional arguments first. For example, to specify a search mode while keeping the default exact match, include 0 as the fifth argument.

Basic exact-match entry example

Suppose A2:A5 contains product codes P-101, P-102, P-103, and P-104, while B2:B5 contains their prices: 12.00, 18.50, 7.25, and 24.00. To find the price of P-102 directly, enter:

With product codes in A2:A5 and prices in B2:B5, this formula searches for P-102 and returns its price:

=XLOOKUP("P-102",A2:A5,B2:B5,"Not found")

The result is 18.50. XLOOKUP compares the code against the cells in A2:A5 and returns the value from the same relative position in B2:B5. Because exact match is the default, the formula would also work without explicitly specifying a match mode.

For a reusable formula, put the code to search for in D2. With codes in A2:A5, prices in B2:B5, and the requested code in D2, enter:

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

If D2 contains P-102, the result is 18.50. If D2 contains a code absent from A2:A5, the result is the text Not found. A cell reference makes the formula easier to reuse: change D2 to another code and the returned price updates automatically.

Returning a custom result when there is no match

By default, an unsuccessful search returns #N/A, which can be useful when a missing item should be noticed. For a report or customer-facing worksheet, a clearer message may be preferable. The fourth argument controls that message, and it can also be an empty string if you want the result cell to appear blank.

With employee IDs in A2:A20, employee names in B2:B20, and the ID to search for in D2, this formula displays a helpful message when the ID is absent:

=XLOOKUP(D2,A2:A20,B2:B20,"ID not listed")

If D2 contains an ID in A2:A20, the formula returns the name on the same row. Otherwise it returns ID not listed. With the same layout, this version returns a blank-looking result instead:

=XLOOKUP(D2,A2:A20,B2:B20,"")

An empty string is not the same as a truly empty cell: the formula remains in the cell, and other formulas may treat its result differently from a cell that was never populated. Use a visible message when users need to know a lookup failed; use the blank option when a missing result is expected and the surrounding layout makes that clear.

Finding the last matching entry

When the lookup column contains duplicates, XLOOKUP searches from the first entry to the last by default, so it returns the first match. Set the sixth argument to -1 to search from the bottom upward. This is useful for logs where the latest row for a customer or item represents the current status.

Suppose A2:A6 contains item IDs, including repeated IDs, and B2:B6 contains their recorded statuses. With the ID to find in D2, this formula returns the status from the last matching row:

=XLOOKUP(D2,A2:A6,B2:B6,"No record",0,-1)

The result is the value in column B on the last row where column A equals D2. The 0 explicitly requests exact matching, and -1 sets the search direction to last-to-first. If the IDs are unique, reversing the search does not change the answer; it matters when a value appears more than once.

Approximate matches for thresholds

XLOOKUP can match a value to a threshold rather than requiring an identical entry. Match mode -1 means exact match or, if there is no exact match, the next smaller item. Match mode 1 means exact match or the next larger item. These options are useful for banding values into categories, but the threshold list must be designed carefully so the selected boundary has the intended meaning.

Suppose A2:A6 contains ascending score thresholds 0, 60, 70, 80, and 90, and B2:B6 contains F, D, C, B, and A. If D2 contains a score of 87, use this formula:

=XLOOKUP(D2,A2:A6,B2:B6,"No grade",-1)

The result is B, because 80 is the greatest threshold less than or equal to 87. An exact score of 90 returns A. A score below the smallest threshold returns No grade, since there is no smaller threshold to use. For mode -1, thresholds should be in ascending order so the intended lower boundary is clear.

Wildcard searches for partial text

Wildcard matching can help when you know only part of a text value. Set match mode to 2 to enable wildcards: an asterisk represents any number of characters, and a question mark represents one character. A tilde is used to search for a literal asterisk or question mark. Wildcards are not enabled by default, so include the match-mode argument.

Suppose A2:A5 contains product names and B2:B5 contains their category names. With the partial name in D2, this formula finds the first product name beginning with the text in D2 and returns its category:

=XLOOKUP(D2&"*",A2:A5,B2:B5,"No match",2)

If D2 contains Desk and the first matching name is Desk Lamp, the formula returns the category in the corresponding B cell. The asterisk is joined to the search text with the ampersand. Because the formula returns the first matching entry, several product names beginning with the same text may produce the same first result each time. For a search that should distinguish a particular character, use a question mark in the lookup pattern.

Two-way lookups and multiple-column results

For a value at the intersection of a row and a column, one XLOOKUP can find the row and a second can find the column. The inner formula returns the relevant data column; the outer formula then selects the requested row from that column.

Suppose A2:A4 contains employee names, B1:D1 contains month headings, and B2:D4 contains the employees’ sales. Put the employee name to find in F2 and the month in G2. With that layout, this formula returns that employee’s sales for the selected month:

=XLOOKUP(F2,A2:A4,XLOOKUP(G2,B1:D1,B2:D4),"Not found")

If F2 contains Ana, G2 contains March, and Ana’s March sales are 240, the result is 240. The inner lookup finds the March heading and supplies the March values from B2:D4; the outer lookup finds Ana in A2:A4 and returns her corresponding value.

XLOOKUP can also return more than one adjacent column at once. With product codes in A2:A5, descriptions in B2:B5, stock counts in C2:C5, and the requested code in E2, enter:

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

If the code is found, the result spills into two neighboring cells: the matching description and stock count. Keep those destination cells empty, or Excel may show a #SPILL! error. Dynamic array results are supported in current Microsoft 365 and Excel 2021 or later.

How to choose the right XLOOKUP options

Start with the simplest formula that describes the task. Most everyday lookups need only the lookup value, lookup array, return array, and possibly a not-found message. Adding options unnecessarily makes formulas harder to read and can hide an incorrect assumption about the data.

Use the default exact match for IDs, names, product codes, and other values that should match precisely.
Set match mode -1 or 1 only when matching a value to sorted thresholds or nearby values is intentional.
Set match mode 2 for wildcard patterns; otherwise asterisks and question marks are treated as ordinary text.
Set search mode -1 when the last duplicate is the desired record.
Use a specific not-found message when readers need to distinguish a missing record from a blank return value.

Binary search modes 2 and -2 can be faster on very large, correctly sorted arrays, but they depend on the lookup array being sorted in the required direction. An unsorted array can produce incorrect results. For ordinary worksheets, the default search is usually the safer choice.

Common XLOOKUP errors and fixes

A formula can be syntactically valid and still return an unexpected result. Check the data layout and arguments before assuming the function is at fault.

#N/A: There is no match, or the formula does not recognize the lookup value as equal to an entry. Add an if-not-found value if appropriate, then check spelling, extra spaces, and data types.
#VALUE!: The lookup and return arrays may have incompatible dimensions. A vertical lookup range should normally be paired with a return range having the same number of rows; a horizontal lookup should use ranges with matching columns.
#SPILL!: A multi-column or multi-row result cannot expand because a destination cell is occupied. Clear the obstructing cells.
Unexpected approximate result: Confirm the match mode and the order of the thresholds. Exact matching is the default; approximate matching happens only when a mode requests it.
Unexpected duplicate result: The default search returns the first match. Use search mode -1 if the last matching record is required.
Formula not recognized: Check whether the Excel version supports XLOOKUP. Also check regional separators if Excel flags the formula syntax.

Numbers stored as text are another frequent source of trouble. A numeric ID such as 0012 may be stored as text in one place and as the number 12 in another; those values are not necessarily interchangeable, especially when leading zeros are meaningful. Standardize the source data rather than changing the lookup blindly. Extra spaces can likewise prevent exact text matches; cleaning the source list is often more reliable than adding complexity to every lookup formula.

XLOOKUP compared with VLOOKUP

VLOOKUP searches the first column of a table and returns a value from a specified column to its right. XLOOKUP separates the search range from the return range, so the return column can be to the left or right. XLOOKUP also defaults to exact matching and has a built-in not-found argument, while older VLOOKUP formulas commonly need an additional error-handling function for a custom missing-value message.

VLOOKUP remains useful when sharing workbooks with people using older Excel versions that do not support XLOOKUP. If compatibility with those versions is not a concern, XLOOKUP is often easier to maintain because inserting columns does not require updating a numeric return-column index. For an older workbook, INDEX and MATCH can provide flexible lookups, though the formula is more involved.

Frequently Asked Questions

What is the simplest XLOOKUP formula?
Use =XLOOKUP(lookup_value,lookup_array,return_array). Replace the argument names with a value or cell reference, the range to search, and the aligned range containing the result. It performs an exact match by default.

How do I make XLOOKUP return blank instead of #N/A?
Add an empty string as the fourth argument. With a lookup value in D2, search values in A2:A10, and results in B2:B10, enter =XLOOKUP(D2,A2:A10,B2:B10,""). The formula returns a blank-looking result when there is no match.

Can XLOOKUP search to the left?
Yes. The lookup array and return array are separate arguments, so the return range can be either left or right of the search range. Make sure both ranges have corresponding positions.

Why does my XLOOKUP return the wrong duplicate?
XLOOKUP searches from first to last by default and returns the first match. Add 0 as the match-mode argument and -1 as the search-mode argument to search from last to first: =XLOOKUP(D2,A2:A10,B2:B10,"Not found",0,-1).

Does XLOOKUP work in Excel 2019?
XLOOKUP is not available in Excel 2019. Use a compatible alternative such as VLOOKUP or INDEX and MATCH when a workbook must run in that version, or open it in Microsoft 365 or Excel 2021 or later.

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.