Spreadsheet Practice

How to speed up XLOOKUP on large sorted data

Formula (Excel and Google Sheets)

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

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.

Result on the example
$315.60 in F2
Works in
Excel 2021, 2024 and Microsoft 365
Google Sheets
Same formula

See it work

The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.

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.

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

Result315.6

  1. F1100427

    The value in F1.

  2. A2:A1312 rows × 1 col: 100112, 100145, 100203, 100268, …

    A range: 12 cells in one column.

  3. B2:B1312 rows × 1 col: $84.20, $19.99, $240.00, $57.35, …

    A range: 12 cells in one column.

  4. XLOOKUP(F1,A2:A13,B2:B13,"Not found",0,2)315.6

    XLOOKUP: Searches one range and returns the matching item from another.

    • lookup_valueF1100427
    • lookup_arrayA2:A1312 rows × 1 col: 100112, 100145, 100203, 100268, …
    • return_arrayB2:B1312 rows × 1 col: $84.20, $19.99, $240.00, $57.35, …
    • if_not_found"Not found"
    • match_mode0
    • search_mode2

In Google Sheets

The same formula works in Google Sheets: =XLOOKUP(F1,A2:A13,B2:B13,"Not found",0,2). We ran it in Google Sheets mode on the same data and it returns $315.60.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=XLOOKUP(F1,A2:A13,B2:B13,"Not found")$315.60The 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")$315.60The 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.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Speed up XLOOKUP on large sorted data

The transaction IDs are sorted from smallest to largest. In F2, return the amount for the ID in F1 using a binary search.

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.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.