Excel XLOOKUP Search Mode: Options, Rules, and Examples

Formulas & functions · Updated November 19, 2026

In Excel, XLOOKUP’s search_mode argument controls whether the function searches from first to last, last to first, or uses a binary search on sorted data. Use 1 for the default forward search, -1 to search backward, 2 for ascending sorted data, and -2 for descending sorted data.

What XLOOKUP search_mode controls

The full syntax is =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). The final two arguments are optional. match_mode determines what counts as a match; search_mode determines the direction or search method used to look through the lookup array. They solve different problems, even though both can affect which result is returned.

For example, imagine a log in which the same order number appears several times because its status was recorded at different times. A top-to-bottom search returns the first recorded status. A bottom-to-top search returns the last one, which may be the current status if newer records are always added at the bottom. Search mode does not sort the data or change the order of rows; it only changes how XLOOKUP looks through the lookup array.

In Microsoft 365 for Windows, enter the desired mode as the sixth argument in the formula. If you omit it, XLOOKUP searches from first to last. You can also omit earlier optional arguments by supplying commas to move to a later one, but including the intervening arguments explicitly is often easier to read.

The four search modes

  • 1 — Search first to last. This is the default, so omitting search_mode has the same effect.
  • -1 — Search last to first. This is useful when the latest matching row is the one you want.
  • 2 — Use binary search on data sorted in ascending order.
  • -2 — Use binary search on data sorted in descending order.

The values 2 and -2 are not simply faster versions of the ordinary search. They rely on the lookup array being sorted in the specified direction. If it is not, the result can be incorrect; Excel does not necessarily warn you that the order is wrong. Use these modes only when the data’s ordering is dependable and maintained.

For everyday unsorted lists, keep the default 1, or use -1 if you specifically need the last matching row. Binary search can reduce the number of comparisons on large, correctly sorted lookup arrays, but it is not a safe shortcut for an unsorted table.

Finding the first or last duplicate

The clearest practical difference between modes 1 and -1 appears when the lookup value occurs more than once. Suppose the rows below contain a product’s successive recorded prices, with the oldest entry at the top and newest at the bottom:

  • A2:A5 contains product codes: P-104, P-205, P-104, P-310.
  • B2:B5 contains recorded prices: 12.50, 8.00, 13.25, 4.50.

With product codes in A2:A5 and prices in B2:B5, this formula searches from the top and returns 12.50, the price in the first matching row:

=XLOOKUP("P-104",A2:A5,B2:B5,"Not found",0,1)

With the same ranges and data, this version searches upward from the bottom and returns 13.25, the price in the last matching row:

=XLOOKUP("P-104",A2:A5,B2:B5,"Not found",0,-1)

The formulas use match mode 0 for an exact match and make the not-found result explicit. Exact matching is already XLOOKUP’s default, but showing it here makes the distinction between the fifth and sixth arguments easier to see. In a real workbook, “last row” means the last matching cell in the lookup array’s current order, not necessarily the newest record. If rows are sorted or rearranged, that meaning changes.

When binary search is appropriate

Binary search repeatedly narrows the range it needs to inspect, rather than checking each row in sequence. That can be useful for very large lookup arrays, especially when their order is maintained. For ascending mode 2, the lookup array must run from lower to higher values; for descending mode -2, it must run from higher to lower values. Text order must also be consistent with Excel’s sort order.

For example, suppose A2:A5 contains ascending numbers 10, 20, 30, 40, and B2:B5 contains their labels: Low, Medium-low, Medium-high, High. With the lookup number in E2 and the lookup and return ranges as described, this exact-match binary search returns Medium-high when E2 is 30:

=XLOOKUP(E2,A2:A5,B2:B5,"Not found",0,2)

For a descending example, suppose A2:A5 contains 90, 70, 50, 30 and B2:B5 contains the corresponding labels: A, B, C, D. With the lookup number in E2 and those ranges, this formula returns C when E2 is 50:

=XLOOKUP(E2,A2:A5,B2:B5,"Not found",0,-2)

Do not use a binary mode just because a list looks roughly sorted. A single misplaced value can undermine the search. If new data is appended, imported, or manually edited, confirm that the lookup array remains in the required order before relying on binary search. Also avoid treating binary search as a way to choose the first or last duplicate: if a duplicate’s precise position matters, use the linear modes 1 or -1.

Search mode is different from match mode

One common source of mistakes is confusing the two optional arguments. Match mode accepts 0 for exact match (the default), -1 for exact match or the next smaller item, 1 for exact match or the next larger item, and 2 for wildcard matching. Search mode accepts 1, -1, 2, or -2 as described above. The same numbers can therefore mean different things depending on their position.

For instance, match mode -1 does not mean “search backward.” It means “match exactly, or return the next smaller item.” Search mode -1, placed in the sixth argument, means “search from last to first.” Likewise, match mode 2 enables wildcard matching, whereas search mode 2 requests a binary search on ascending data.

With ascending thresholds 10, 20, 30, 40 in A2:A5, corresponding labels in B2:B5, and the requested value 27 in E2, this formula uses match mode -1 to return the next smaller threshold’s label, the label beside 20. Its search mode is 1, so it searches normally from first to last:

=XLOOKUP(E2,A2:A5,B2:B5,"Not found",-1,1)

That example can be useful for threshold or band lookups. If you change its final argument to 2, the array must be sorted ascending for binary search. The match behavior and the search method remain separate decisions.

How to choose the right mode

Choose based on what the rows mean, not just on which option sounds faster:

  • Use 1 when you want the first match in the current row order or have no special reason to search backward.
  • Use -1 when the last matching row is meaningful, such as a latest entry stored at the bottom of an unsorted log.
  • Use 2 or -2 only when the lookup array is reliably sorted ascending or descending, respectively.
  • Use match mode separately to choose exact, approximate, or wildcard matching.

A useful decision question is: “If there are duplicates, which row should win?” If the answer is “the first occurrence,” use 1; if it is “the last occurrence,” use -1. If duplicates are not relevant and the array is sorted and stable, a binary mode may be an option. For a typical employee list, product table, or unsorted export, the default linear search is usually the least fragile choice.

Common problems and checks

  • The wrong duplicate is returned: Check whether the formula uses 1 or -1, then check the current row order. A reverse search returns the last match in that order, not automatically the most recent date.
  • A binary formula gives an unexpected result: Verify the entire lookup array’s sort order, including recently added rows. Use 1 or -1 if sorting cannot be guaranteed.
  • The formula returns “Not found”: Check for extra spaces, inconsistent number-versus-text values, or a lookup value that is not actually present. The fourth argument controls the displayed not-found result; it does not alter the search direction.
  • The function is not recognized: Check the Excel version. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. Excel 2016 and Excel 2019 do not include XLOOKUP as a built-in function.

Search mode also does not repair mismatched ranges. The lookup array and return array should correspond row by row and have compatible dimensions. If the formula looks correct but returns a value from an unexpected row, inspect the ranges as well as the search mode.

Availability and compatibility

The examples use Microsoft Excel for Microsoft 365 on Windows. XLOOKUP is also available in supported Microsoft 365 versions for Mac and Excel for the web; the arguments and search-mode values work the same way. You do not need a different search-mode setting on those platforms. Ribbon locations are not central to this feature because the setting is entered in the formula itself.

When a workbook containing XLOOKUP is opened in an older Excel release that does not support the function, the formula may display an unsupported-function error or a name such as _xlfn.XLOOKUP. If the workbook must work in Excel 2016 or 2019, consider using a compatible alternative such as INDEX and MATCH, or provide a version of the workbook without XLOOKUP formulas. Check the actual Excel versions used by collaborators before distributing a workbook that depends on newer functions.

Frequently Asked Questions

What is the default XLOOKUP search mode?
It is 1, which searches from first to last. You can leave out the sixth argument to use this default.

How do I make XLOOKUP return the last match?
Set the sixth argument, search_mode, to -1. This searches from last to first and returns the last matching row in the lookup array’s current order.

Does search mode -1 mean “next smaller”?
No. Search mode -1 means search backward. Match mode -1, which is the fifth argument, means exact match or next smaller item.

Can I use search mode 2 on unsorted data?
No. Mode 2 requires ascending order, and mode -2 requires descending order. On incorrectly sorted data, a binary search can return an incorrect result without a clear warning.

Which mode should I use for an ordinary lookup?
Use the default mode 1 for an ordinary first-match lookup. Use -1 only when the last matching row is the intended result; use binary modes only when sorting is guaranteed.

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.