XLOOKUP Binary Search: How It Works

XLOOKUP’s binary search mode is a faster lookup method that assumes your data is sorted, and it can return incorrect results if the data isn’t sorted correctly. This article explains exactly how binary search works in XLOOKUP, when to use it, and how to avoid the common mistakes that trip up most users.

What Is Binary Search in XLOOKUP?

Binary search is an algorithm that finds an item in a sorted list by repeatedly dividing the search area in half. In XLOOKUP, you can choose this search mode by setting the fifth argument (match_mode) to 1 (exact match, binary search) or -1 (wildcard, binary search). The default is 0 (exact match, linear search), which scans every row from top to bottom.

Binary search is much faster on large datasets because it checks only about log₂(n) rows instead of n rows. For 1 million rows, that’s about 20 comparisons versus up to 1 million. However, this speed comes with a strict requirement: the lookup array must be sorted in ascending order (for 1) or descending order (for -1). If the data isn’t sorted correctly, binary search can return wrong results—even if the exact lookup value exists in the data.

How Binary Search Works Step by Step

Here’s a simple example. Suppose your lookup array is {1, 5, 10, 42, 100} and you’re looking for 42.

1. Start with the whole array: indices 1 to 5. The middle is index 3 (value 10).
2. Since 42 > 10, discard the left half (indices 1–3). Now search indices 4–5.
3. The middle of that subarray is index 4 (value 42). That’s a match.
4. Return the corresponding result.

If the value isn’t present, binary search stops when the search range is empty. In 1 (exact match) mode, it returns #N/A. In -1 (wildcard) mode, it returns the nearest value—but only if the data is sorted correctly.

When to Use Binary Search (and When Not To)

Use binary search when:

• Your lookup array is already sorted (ascending for 1, descending for -1).
• You have thousands or millions of rows and need speed.
• You’re doing repeated lookups in a large static table.

Don’t use binary search when:

• Your data is unsorted or sorted by a different column.
• You need exact matches and the data might contain duplicates—binary search doesn’t guarantee which duplicate you get.
• Your data is small (under a few thousand rows); linear search is fast enough and safer.

Common Mistakes and How to Avoid Them

The biggest mistake is using binary search on unsorted data. For example, if your lookup array is {10, 5, 42, 1} and you search for 42, binary search might compare against the middle (5), then the right half (42), and find it—but it could also miss it depending on the arrangement. The result is unpredictable.

Another mistake is assuming that binary search with 1 (exact match) will find the first occurrence of a duplicate. It doesn’t—it finds any occurrence, and which one depends on the data layout. If you need the first or last duplicate, use linear search or sort the data and use additional logic.

Also, remember that -1 (wildcard) mode is not for approximate matching—it’s for wildcard characters like * and ?. If you want an approximate match (closest value), you need to use match_mode = -1 with sorted data, but that’s a different feature (approximate match) that works like VLOOKUP’s approximate match.

Binary Search vs. Linear Search: Performance and Safety

Linear search (the default) scans every row until it finds a match. It’s simple and always correct, but O(n) time. Binary search is O(log n), which is dramatically faster on large data. For example, on 100,000 rows, linear search might check 50,000 rows on average, while binary search checks at most 17.

However, the safety trade-off is real: linear search works on unsorted data, binary search doesn’t. If you’re not 100% sure your data is sorted, use linear search. Many experienced Excel users never use binary search because the performance gain is rarely noticeable in typical spreadsheets (under 100k rows), and the risk of silent errors is high.

How to Sort Data for Binary Search

To use binary search safely, sort the lookup array:

1. Select the column(s) that contain the lookup values.
2. Go to Data > Sort (or use the Sort button on the Home tab).
3. Choose ascending for match_mode 1, descending for -1.
4. If you have associated data, sort the entire table by the lookup column.

Be careful with numbers stored as text—they sort differently. A common issue is that numbers formatted as text sort after real numbers, so binary search might not find them. Use the VALUE() function or convert text to numbers before sorting.

Frequently Asked Questions

Why does XLOOKUP return #N/A even though the value exists?

If you’re using binary search and the data isn’t sorted correctly, XLOOKUP may skip over the value and return #N/A. Check that your lookup array is sorted in the exact order required (ascending for match_mode 1, descending for -1). Also, ensure there are no hidden characters or text-formatted numbers.

Can I use binary search with unsorted data?

Technically you can, but the results are unreliable. Binary search assumes the data is sorted; if it isn’t, you might get wrong matches or #N/A. Always sort your data first, or use the default linear search (match_mode 0) for unsorted data.

What’s the difference between match_mode 1 and -1?

Both use binary search. 1 means exact match (binary search), which requires ascending sort. -1 means wildcard match (binary search), which requires descending sort. The wildcard mode allows * and ? in the lookup value, but the data must still be sorted.

Is binary search faster in Excel?

Yes, binary search is significantly faster on large datasets. However, for typical spreadsheet sizes (under 100,000 rows), the difference is usually imperceptible. The speed advantage becomes important when you have millions of rows or are doing thousands of lookups in a single calculation.

Does binary search work with approximate matches like VLOOKUP?

No. XLOOKUP’s binary search is for exact matches (mode 1) or wildcards (mode -1). For approximate matches (closest value), you need to use match_mode = -1 with the search_mode argument set to 1 (binary search) or 2 (binary search, descending). That’s a separate feature.

How do I know if my data is sorted correctly?

Use Excel’s sort feature to sort the lookup column. For ascending, numbers go from smallest to largest, and text goes alphabetically. For descending, the reverse. You can also use the ISNUMBER() and ISTEXT() functions to check data types, but the simplest is to visually inspect or use conditional formatting to highlight duplicates.

Related

• Unbox ASMR Admin Abuse Schedule?
• How to Duplicate a Menu in WordPress
• How to Choose a Task System as a Solo Dev

in t | Lookup Value | - | 42 | 0
in d | Lookup Array (comma-separated) | - | 1, 5, 10, 42, 100 | 0
out result | Result | - | 42 | 0