# How to speed up XLOOKUP on large sorted data

Tell XLOOKUP the data is sorted with search mode 2: `=XLOOKUP(F1,A2:A13,B2:B13,"Not found",0,2)`. It then halves the list at each step instead of reading every row.

**Excel:** `=XLOOKUP(F1,A2:A13,B2:B13,"Not found",0,2)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Txn ID | Amount |  |  | Txn ID | 100427 |
| 100112 | 84.2 |  |  | Amount |  |
| 100145 | 19.99 |  |  |  |  |
| 100203 | 240 |  |  |  |  |
| 100268 | 57.35 |  |  |  |  |
| 100311 | 112.8 |  |  |  |  |
| 100377 | 9.5 |  |  |  |  |
| 100427 | 315.6 |  |  |  |  |
| 100490 | 48 |  |  |  |  |
| 100552 | 76.25 |  |  |  |  |
| 100618 | 199 |  |  |  |  |
| 100673 | 25.4 |  |  |  |  |
| 100741 | 133.75 |  |  |  |  |

Formula in F2: `=XLOOKUP(F1,A2:A13,B2:B13,"Not found",0,2)`

Result: $315.60

## How it works

A normal exact lookup checks rows one by one. Search mode `2` is a binary search: XLOOKUP looks at the middle row, decides which half the ID must be in, and repeats. On 100,000 sorted rows that is about 17 checks instead of up to 100,000, which matters when thousands of formulas do lookups.

## Other ways

- `=XLOOKUP(F1,A2:A13,B2:B13,"Not found")`: The ordinary linear search: same result, and the right choice for small or unsorted lists.
- `=IF(VLOOKUP(F1,A2:A13,1,TRUE)=F1,VLOOKUP(F1,A2:B13,2,TRUE),"Not found")`: The classic fast exact lookup for older versions: two approximate (binary) VLOOKUPs, the first checking that the ID really exists.

## Common mistakes

- Binary search on unsorted data returns wrong results or "Not found" without warning. Use 2 for ascending order and -2 for descending.
- On sheets with a few hundred rows, the difference isn't noticeable. Keep the simpler formula there.

## Questions

**Does Google Sheets support search mode 2?**

Yes. XLOOKUP in Google Sheets accepts 2 and -2 for binary search.

**What else makes lookups slow?**

Whole-column ranges in array formulas, volatile functions like OFFSET and INDIRECT, and repeating the same lookup in many cells instead of once with LET.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/xlookup-binary-search