Excel XLOOKUP Sort Order: How Search Direction Works

Formulas & functions · Updated November 19, 2026

In Excel, XLOOKUP does not have a separate sort-order argument: its sixth argument, search_mode, controls the direction or method of searching. The default searches from the first item to the last; use -1 to search from last to first, or use 2 and -2 for binary searches on ascending and descending sorted data.

What “sort order” means in XLOOKUP

The phrase “XLOOKUP sort order” can mean two different things: the order of the values in the lookup column, or the direction in which XLOOKUP checks those values. XLOOKUP does not sort a range, rearrange rows, or offer an argument that changes the worksheet’s sort order. Its search settings determine how it looks through the lookup array and, in binary-search mode, what order that array must already be in.

For ordinary lookups, the lookup array can be unsorted. XLOOKUP searches it linearly from top to bottom by default, and returns a result for the first matching item it encounters. Changing to a reverse search makes it look from bottom to top instead. Sorting the range may still be useful for readability or other calculations, but it is not required for these linear searches.

Sorted data becomes essential when you choose a binary search. Binary search is designed to cut down the number of comparisons by repeatedly narrowing the part of the array it checks. It relies on the values being in the stated ascending or descending order; if they are not, XLOOKUP can return an incorrect result.

XLOOKUP’s arguments and where search mode fits

The function has six arguments, with the last three optional: XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). The first three identify what to find, where to look, and which corresponding value to return. The optional arguments then control what counts as a match, what to display if there is no match, and how to search.

  • if_not_found supplies a chosen result when there is no match. If omitted, XLOOKUP returns #N/A.
  • match_mode controls the match rule: 0 is exact, -1 is exact or next smaller, 1 is exact or next larger, and 2 allows wildcard matching.
  • search_mode controls search direction or method: 1 is first-to-last, -1 is last-to-first, 2 is binary ascending, and -2 is binary descending.

The default values are 0 for match_mode and 1 for search_mode. This means a basic XLOOKUP uses an exact match and searches from the beginning of the lookup array. Because the arguments are positional, a formula that specifies search_mode must also supply the earlier optional arguments, even if they are just 0 or an empty-result message.

First-to-last versus last-to-first searches

Use the default search_mode of 1 when you want the first matching row in the lookup array. This is usually the clearest choice for a list of unique IDs, product codes, or names. If the lookup value occurs more than once, the position of the first occurrence determines the returned value.

Use -1 when the last occurrence is the one you need. A common example is a transaction log where entries are added over time and you want the latest recorded value for a key. This does not calculate the maximum date or sort the records: it assumes that the final matching row is the row you want. If records are not arranged chronologically, reverse search alone will not identify the newest transaction.

Suppose IDs are in A2:A6 and corresponding prices are in B2:B6. The ID 104 appears in rows 3 and 6, with prices 12.50 and 15.00. To return the price from the last matching row, use this layout and formula:

With IDs in A2:A6 and prices in B2:B6, enter:

=XLOOKUP(104,A2:A6,B2:B6,"Not found",0,-1)

The result is 15.00 because the reverse search encounters the match in row 6 first. With the same layout and a search mode of 1, the result would be 12.50. The exact-match setting is included explicitly here to make clear that the formula is not looking for a nearby ID.

Binary searches and required sort order

Binary search can be useful with large, static lookup lists, but only when the lookup array is properly sorted. Set search_mode to 2 for ascending order, such as numbers from smallest to largest, or -2 for descending order, such as numbers from largest to smallest. Unlike the linear modes, these settings depend on the order of the data. An unsorted array can produce an incorrect result rather than a reliable warning that the data is out of order.

For example, suppose product IDs are in A2:A6 in ascending order—101, 102, 103, 104, 105—and prices are in B2:B6. To search that sorted list in binary ascending mode, use:

With ascending IDs in A2:A6 and prices in B2:B6, enter:

=XLOOKUP(104,A2:A6,B2:B6,"Not found",0,2)

This returns the price aligned with ID 104. If the IDs were instead arranged 105, 104, 103, 102, 101, the corresponding binary search mode would be -2. The values in the return array do not have to be sorted in the same order; it is the lookup array—the range being searched—that must meet the binary mode’s ordering requirement.

Binary search and approximate matching are separate settings. For instance, match_mode -1 means “exact match or next smaller value,” while search_mode -1 means “search from last to first.” The same number has a different meaning depending on which argument it occupies. An approximate binary lookup therefore needs both choices stated deliberately.

Choosing a match mode for sorted data

Sorting is also relevant when the task is to find a nearest threshold rather than an exact key. With numeric breakpoints in ascending order, match_mode -1 can return an exact match or the next smaller value, while 1 can return an exact match or the next larger value. These settings define the matching rule; they do not themselves sort the range or select binary search. You choose search_mode separately.

Imagine a rate table with breakpoints 0, 100, and 500, each paired with a rate. A value of 250 with next-smaller matching should use the row for 100; next-larger matching should use the row for 500. This sort of threshold lookup needs careful thought at the ends of the range: a lookup below the smallest breakpoint may have no next-smaller result, and a lookup above the largest may have no next-larger result. Decide what the formula should return in those cases.

For broad compatibility and easier review, many workbooks use a linear search with an explicit match mode unless the binary method has a clear benefit. If you do use binary search, document the required order and keep the lookup array sorted after updates. A formula cannot make an unsorted list safe simply by adding 2 or -2.

Duplicates, blanks, and choosing the right result

Duplicates are not automatically an error. They make search direction important: a forward linear search returns the first match, while a reverse linear search returns the last. Before choosing, decide what “the right row” means in the data. It might be the earliest record, the latest appended record, or a particular record identified by another field.

For instance, if a customer appears several times in a sales table, reverse search can return the amount on the bottom-most matching row. That is only the latest sale if the rows are actually ordered by sale date. If dates can be entered late or the table is sorted by another column, use a method that explicitly identifies the maximum date or filters the matching records rather than assuming row position represents time.

Blanks and inconsistent data can also affect a lookup. A blank cell in a lookup array is not the same thing as a missing match, and values that look identical may differ because one is stored as text and another as a number. Extra spaces in text keys can likewise prevent an exact match. When results seem wrong, inspect the actual values and data types in the lookup and return ranges before changing search mode.

Common mistakes and how to troubleshoot them

  • Putting the setting in the wrong argument: The fifth optional argument is match_mode; the sixth is search_mode. A -1 placed fifth means exact-or-next-smaller, not reverse search.
  • Using binary mode on an unsorted range: Set the lookup array into the specified ascending or descending order first, or use a linear mode.
  • Assuming XLOOKUP sorts rows: It returns a corresponding value; it does not reorder the source data or produce a sorted result.
  • Expecting the last date from a reverse search: Reverse search returns the last matching position, not necessarily the greatest date. Check the table’s row order.
  • Leaving duplicate behavior implicit: Decide whether the first or last matching occurrence is intended, then set the search direction clearly.
  • Omitting optional arguments accidentally: To specify the sixth argument, include the fifth argument first. Use a value such as 0 for exact matching, or provide an if_not_found message in the fourth position.

If a formula returns #N/A, first verify that the lookup value exists exactly as stored. If it returns a plausible but unexpected result with binary search, verify the order of the entire lookup array, not just the first and last entries. For approximate matching, confirm both the intended next-smaller or next-larger rule and the boundary behavior.

Availability and alternatives

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including supported editions for Windows, Mac, and the web. The function syntax and search-mode meanings are the same across those platforms, although the ribbon layout can differ. Excel 2016 and Excel 2019 do not include XLOOKUP; a workbook containing it may show a compatibility error such as #NAME? in those versions.

In older Excel versions, INDEX with MATCH can provide a similar lookup, and a reverse search can sometimes be built with other formula patterns. Those alternatives have different syntax and limitations, so a workbook shared with older versions should be checked in the actual target version. For a simple exact lookup in a modern Excel file, XLOOKUP’s explicit match and search arguments are usually easier to read than a workaround.

On Windows, XLOOKUP is entered in a worksheet cell just like other formulas; there is no separate search-order menu to configure. The same is true in Excel for Mac and Excel for the web. If the lookup list is expected to grow, consider using an Excel Table so the lookup and return columns expand with new rows, while still checking that any binary-search lookup column remains correctly sorted.

Frequently Asked Questions

Does XLOOKUP require the lookup column to be sorted?
No, not for the default or reverse linear searches. Sorting is required when you use binary search mode 2 or -2; the lookup array must be in ascending or descending order as specified.

How do I make XLOOKUP return the last match?
Set the sixth argument, search_mode, to -1. Keep the fifth argument for the match rule, such as 0 for an exact match. The last match means the bottom-most matching row in the lookup array, not automatically the most recent date.

What is the difference between match_mode -1 and search_mode -1?
match_mode -1 asks for an exact match or the next smaller item. search_mode -1 searches from the last item toward the first. They control different aspects of XLOOKUP and occupy different argument positions.

Can XLOOKUP search a descending list?
Yes. Use search_mode -2 for binary search on a descending lookup array. For a normal linear search, the array can be descending, ascending, or unsorted.

Does XLOOKUP sort the results it returns?
No. It returns the value from the corresponding position in the return array. To create a sorted output, sort the source data separately or use a suitable sorting function; changing XLOOKUP’s search mode only changes how it searches.

Why does binary XLOOKUP return the wrong value?
The lookup array may not be sorted in the direction required by the selected binary mode, or the formula may use the wrong mode. Check the full lookup range and remember that 2 means ascending binary search while -2 means descending binary search.

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.