XLOOKUP in Excel: Syntax, Examples, and Tips

Formulas & functions · Updated November 20, 2026

XLOOKUP is an Excel function that searches one range for a value and returns the corresponding value from another range. In Microsoft 365 for Windows, its basic form is =XLOOKUP(D2,A2:A6,B2:B6,"Not found"): look for the value in D2 in A2:A6, then return the matching item from B2:B6.

What XLOOKUP Does

Lookup formulas answer questions such as “What product name belongs to this code?” or “What price matches this item?” XLOOKUP separates the range to search from the range to return a result from. That makes the formula easier to read and more flexible than older lookup methods that rely on a fixed column position.

The function normally looks for an exact match, and it can return results from either side of the lookup range. It can also search vertically down columns or horizontally across rows. If you ask it to return several adjacent columns or rows, it can return an array of results that spills into neighboring cells, provided those cells are clear.

A lookup is not the same as a calculation over every matching record. By default, XLOOKUP returns one match: the first one it finds. If a key appears several times, that behavior matters. You can search from the end instead, but XLOOKUP does not automatically combine repeated records or total their values.

XLOOKUP Syntax and Its Arguments

XLOOKUP has six arguments. The first three are required; the last three are optional. Their order is important because the not-found message, match mode, and search direction each have a specific position.

  • lookup_value: The value to find, such as a product code in D2.
  • lookup_array: The single row or column in which Excel searches.
  • return_array: The row, column, or compatible range from which Excel returns a result.
  • if_not_found: What to return when there is no match. If omitted, XLOOKUP returns #N/A.
  • match_mode: Whether to seek an exact match, an approximate match, or a wildcard match.
  • search_mode: Which direction to search and, optionally, whether to use a binary search.

The lookup and return ranges need corresponding positions: the first item in the return range belongs to the first item in the lookup range, the second belongs to the second, and so on. Their shapes must be compatible. For example, searching a column and returning a separate column with the same number of rows is a straightforward arrangement. If range dimensions do not line up, the formula may return an error.

A Basic XLOOKUP Example

Suppose A2:A6 contains product codes P-101, P-102, P-103, P-104, and P-105, while B2:B6 contains Mug, Notebook, USB hub, Desk lamp, and Keyboard in the same order. D2 contains P-104. With that layout, enter:

=XLOOKUP(D2,A2:A6,B2:B6,"Not found")

The result is Desk lamp. Excel searches A2:A6 for the value in D2, finds P-104 in A5, then returns the value in the corresponding position of B2:B6, which is B5. If D2 contains P-999 instead, the formula returns Not found rather than #N/A.

The fourth argument is useful in reports because it makes an expected missing key easier to interpret. You could use a short label such as “No match” or return an empty-looking result with two quotation marks. Choose a message that distinguishes a missing lookup from a genuinely blank value in your data; otherwise, readers may not know why the result is empty.

Match Modes and Search Direction

By default, XLOOKUP uses exact matching and searches from the first item to the last. You can change those behaviors with the fifth and sixth arguments. In a range containing duplicates, the default direction returns the first match. To retrieve the last occurrence, set the search mode to -1.

For example, suppose A2:A8 lists customer IDs, C2:C8 lists their status updates in chronological order, and E2 contains the customer ID to check. This formula returns the status on the last matching row:

=XLOOKUP(E2,A2:A8,C2:C8,"Not found",0,-1)

The 0 explicitly requests an exact match; -1 searches from the last item toward the first. The result is the status in the final row for that customer, assuming the records are listed in chronological order. If the rows are not chronological, the formula still returns the last listed match, not necessarily the newest update by date.

The match modes are:

  • 0: Exact match. This is the default.
  • -1: Exact match, or the next smaller item if there is no exact match.
  • 1: Exact match, or the next larger item.
  • 2: Wildcard match, using characters such as * and ?.

For a wildcard search, * represents any number of characters and ? represents one character. Use ~ before a wildcard character when you want to search for that character literally. The search modes 2 and -2 use binary search and require the lookup range to be sorted in the appropriate order. An incorrectly sorted range can produce unexpected results, so use the ordinary search mode unless the data is deliberately sorted and you need that search method.

Useful XLOOKUP Patterns

One advantage of XLOOKUP is that its lookup and return ranges are independent. For instance, if A2:A6 contains product names and B2:B6 contains their codes, while E2 contains a code, this formula returns the name to its left:

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

Here, B2:B6 is the search range and A2:A6 is the return range. This is a simple way to look left without rearranging columns. The formula returns the name on the same row as the matching code.

You can also search by more than one condition. Suppose A2:A10 contains employee names, B2:B10 contains regions, C2:C10 contains sales figures, E2 contains a name, and F2 contains a region. The following formula returns the sales value for the row matching both conditions:

=XLOOKUP(1,(A2:A10=E2)*(B2:B10=F2),C2:C10,"Not found")

Each comparison creates a series of TRUE and FALSE values. Multiplying the two series makes a row evaluate to 1 only when both conditions are TRUE; XLOOKUP searches for that 1. This pattern expects a single matching row. If several rows satisfy both conditions, the default first-to-last search returns the first one, not a total or a list of all matching sales.

Approximate matching is useful for ordered bands such as service levels or commission thresholds. Suppose E2:E5 contains ascending minimum values 0, 500, 1000, and 2500, F2:F5 contains the corresponding band names, and B2 contains the value to classify. This formula returns the band for an exact threshold or the next smaller threshold:

=XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,"Not found",-1)

For a value of 800, the result is the name beside 500. The dollar signs keep the threshold and return ranges fixed if you copy the formula to other rows. Make sure the thresholds represent the rule you intend; approximate matching does not validate whether a tier system is logically complete.

Common Problems and How to Fix Them

A missing result is often a data consistency problem rather than a syntax problem. Check for leading or trailing spaces, different punctuation, or a code stored as text in one range and as a number in another. Text matches are not case-sensitive in ordinary XLOOKUP matching, so capitalization alone is usually not the cause. If a match should exist, compare the underlying values and clean or standardize them as needed.

If you see #N/A, XLOOKUP did not find a match and no not-found value was supplied, or the lookup value genuinely is absent. Add the fourth argument if a readable fallback is appropriate. That argument handles a missing match; it does not repair a broken reference or every other formula error. A #VALUE! error can point to incompatible range dimensions, while a #NAME? error may mean the Excel version does not recognize XLOOKUP.

Also check for duplicates and blank source cells. A duplicate key means the default search returns the first occurrence, which may not be the record you intended. A matching return cell that is empty may appear as 0 in the formula result, so distinguish an empty source cell from a lookup that found no key. Finally, when a formula returns an array, make sure cells where the results need to spill are not occupied; a blocked spill range prevents the full result from appearing.

XLOOKUP Compared with VLOOKUP and INDEX/MATCH

VLOOKUP searches the first column of a table and returns a value from a column to its right. Its column number is part of the formula, so inserting or moving columns can make a formula harder to maintain. XLOOKUP names the search and return ranges separately, can return values to the left or right, and uses exact matching by default. This reduces some common sources of mistakes, although it does not remove the need to select the correct ranges.

INDEX/MATCH is another flexible option and remains useful in older Excel versions that do not include XLOOKUP. It can look left or right, but usually requires combining two functions, which may be less immediately readable for new users. XLOOKUP is not automatically better for every workbook: a file shared with people using older Excel versions may need a compatible alternative, and an existing, reliable formula does not necessarily need replacing.

Availability and Compatibility

XLOOKUP is available in Microsoft 365 and in Excel 2021 or later, including Excel 2024. It is also available in Excel for the web and supported current Excel versions for Mac. The basic function works similarly across these platforms; ribbon locations are not needed to enter it, because you can type the formula directly into a cell or the formula bar.

Excel 2019 and Excel 2016 do not include XLOOKUP as a built-in function. If a workbook containing XLOOKUP is opened in an older version, the formula may not calculate correctly there. Before sharing a file, consider the recipient’s Excel version. For compatibility with older installations, use a supported alternative such as INDEX/MATCH or VLOOKUP, taking its direction and exact-match settings into account.

Frequently Asked Questions

Is XLOOKUP exact match by default?
Yes. Its default match mode is exact, and its default search direction is from the first item to the last. You can specify those options explicitly when clarity is important or when you need a different behavior.

Can XLOOKUP return a value from a column to the left?
Yes. Put the range to search in the lookup-array argument and the left-hand range in the return-array argument. Unlike VLOOKUP, XLOOKUP does not require the return range to be to the right.

How do I make XLOOKUP show something instead of #N/A?
Supply a not-found value as the fourth argument, such as a short message or an empty string. This changes the result when no match is found, but it does not hide unrelated errors caused by invalid references or incompatible ranges.

Does XLOOKUP return every matching row?
Not by default. It returns one match, normally the first. Searching from the end can return the last matching record, but a function designed to filter a full list is more appropriate when you need every matching row.

Why is XLOOKUP showing #NAME? in my workbook?
A common reason is opening the workbook in an Excel version that does not support the function, such as Excel 2019 or 2016. Confirm the version being used and, if the file must work there, replace the formula with a compatible lookup method.

Related

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.