XLOOKUP’s search_mode argument sets direction and method: 1 first-to-last (default), -1 last-to-first, 2 binary search ascending, -2 binary search descending. Example: =XLOOKUP("Blue", A2:A6, C2:C6, , 0, -1) returns the LAST row matching Blue, which is how you fetch a customer’s most recent order in one formula.
intermediate 2 min read
XLOOKUP Search Modes: Reverse and Binary Search
Use XLOOKUP search_mode in Google Sheets to search bottom-to-top for the latest entry, or switch on binary search for speed on large sorted ranges.
SB
Sheets Bootcamp
July 28, 2026
Table of Contents
Quick Answer
Most lookups never touch XLOOKUP’s sixth argument. But two real problems live here: “give me the latest entry, not the first” and “this lookup over 80,000 rows is slow.” Search_mode solves both.
In This Guide
- The Four Search Modes
- First Match vs Last Match
- The Latest-Entry Pattern
- Binary Search for Large Data
- Related Google Sheets Tutorials
The Four Search Modes
Formula
=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode]) | search_mode | Behavior | Requires sorting? |
|---|---|---|
| 1 (default) | Scan first to last, return the first match | No |
| -1 | Scan last to first, return the last match | No |
| 2 | Binary search, range sorted ascending | Yes |
| -2 | Binary search, range sorted descending | Yes |
Search_mode is the sixth argument, so using it means filling (or skipping with commas) the two before it. =XLOOKUP(key, range, results, , 0, -1) reads: no custom missing value, exact match, search from the bottom.
First Match vs Last Match
When the lookup range contains duplicates, direction decides which row wins. Take an order log where customers appear repeatedly:
| Customer | Product | Amount |
|---|---|---|
| Dana | Magnifying Glass | $24.99 |
| Marcus | Pocket Watch | $35.00 |
| Dana | Field Binoculars | $65.00 |
| Priya | Cipher Decoder | $28.50 |
| Dana | Forensic Chemistry Set | $45.00 |
The default finds Dana’s first order:
Formula
=XLOOKUP("Dana", A2:A6, C2:C6) That returns $24.99, the top row. Flip the direction:
Formula
=XLOOKUP("Dana", A2:A6, C2:C6, , 0, -1) Now XLOOKUP starts at row 6 and works upward, so the first match it meets is Dana’s last order: $45.00.
Note
Before XLOOKUP, “find the last match” needed a LOOKUP trick or a sorted helper column. Search_mode -1 replaces all of that with one argument.
The Latest-Entry Pattern
Logs, forms, and transaction sheets grow downward: the newest data is at the bottom. That makes search_mode -1 the natural way to ask “what is the current value?”
- Most recent order for a customer: search the customer column bottom-up, return the amount
- Latest status for a ticket: search ticket IDs bottom-up, return the status column
- Current price of a product: in a price-history sheet, search the product bottom-up, return the price
If your log includes several criteria (say, the latest order for a customer AND a specific product), combine search_mode -1 with the multiple criteria pattern, which accepts the same sixth argument.
Binary Search for Large Data
The default search checks rows one at a time until it finds a match. On a few thousand rows that is instant. On tens of thousands of rows with many formulas, it adds up. Binary search takes a different approach: it checks the middle of the range, decides which half the value must be in, and repeats. Each step halves the problem.
Formula
=XLOOKUP(A2, Data!A2:A80000, Data!F2:F80000, "none", 0, 2) The tradeoff is strict: search_mode 2 assumes the lookup range is sorted ascending, and -2 assumes descending. On unsorted data, binary search skips over values and returns wrong or missing results without any error, because the method itself depends on the order.
Warning
Only use binary search when both things are true: the range is large enough that lookups feel slow, and the data is reliably sorted. A sheet that users re-sort freely is not a candidate. When in doubt, stay on the default: correct beats fast.
This is also the one place where sorting matters for XLOOKUP at all. The match modes -1 and 1 work fine on unsorted data with the default search, so keep the sorted-data discipline only where binary search demands it.
Related Google Sheets Tutorials
- XLOOKUP: The Complete Guide: All six parameters in one place
- XLOOKUP Match Modes: What counts as a match
- XLOOKUP with Multiple Criteria: Combine with last-match searches
- XLOOKUP Errors: When lookups return the wrong thing
- SORT Function: Keeping ranges sorted for binary search
Frequently Asked Questions
What is search_mode in XLOOKUP?
Search_mode is XLOOKUP's sixth argument and controls the direction and method of the search: 1 searches first to last (default), -1 searches last to first, 2 runs a binary search on ascending data, and -2 runs a binary search on descending data.
How do I get the last matching value with XLOOKUP?
Set search_mode to -1. For example, =XLOOKUP("Blue", A2:A100, C2:C100, , 0, -1) searches from the bottom up and returns the last row where column A is Blue. This is the easiest way to find a customer's most recent order.
What does binary search do in XLOOKUP?
Binary search (search_mode 2 or -2) repeatedly halves the search range instead of scanning row by row, which is much faster on very large ranges. It requires the lookup range to be sorted, ascending for 2 and descending for -2, and returns unreliable results on unsorted data.
Do I need search_mode for normal lookups?
No. The default (1, first to last) is correct for almost everything. Reach for -1 when you specifically want the latest of several matches, and for 2 only when a lookup over tens of thousands of sorted rows is noticeably slow.
Which rows does XLOOKUP return when there are duplicate matches?
With the default search_mode, XLOOKUP returns the first match from the top. With search_mode -1 it returns the first match from the bottom, which is the last occurrence in the range.
You've googled this formula before. Probably more than once.
Basic Training is a free course inside a real Google Sheet that grades every formula the moment you press Enter. 7 lessons, about 40 minutes, and this loop ends.
Try the Sheet That Grades YouFree. No spam, ever.