XLOOKUP Search Modes 2 and -2: Sorted Tables and Safety Checks

Match mode and search mode do different jobs

XLOOKUP’s fifth argument controls matching: 0 means exact, and −1 means exact or the next smaller key. Its sixth argument controls the search: 2 uses ascending binary search in Excel and −2 uses descending binary search.

In this example, query 1055 is missing under exact matching but matches key 1040 under lower-bracket matching, returning 36.25. Changing search direction must not turn an exact lookup into a bracket lookup.

Keep keys and values together when sorting

A2:B6 contains ascending numeric keys and their rates. C2:D6 contains the same pairs in descending order. Always sort both columns together; sorting just keys disconnects them from their values.

H3 and H4 validate five numeric, strictly ordered keys. This example intentionally rejects duplicates to make the returned row unambiguous. Sorted data can contain equal keys in general, but binary search should not be used to promise first- or last-duplicate selection. Use the appropriate linear search mode when that selection matters.

Compare exact results in both directions

H6 and H7 use exact matching and the corresponding ascending or descending table. At query 1040, both return 36.25. At 1055, both return Missing Key. Blank and nonnumeric query cells produce a message.

=IF(H3<>"OK",H3,IF(NOT(ISNUMBER(E2)),"Enter numeric query",XLOOKUP(E2,A2:A6,B2:B6,"Missing Key",0,2)))

Compare the next-smaller bracket

H8 and H9 use match mode −1. At 1055 they return the rate for 1040; below the smallest key they return Missing Key; above the largest key they return the rate for 1150.

These are illustrative thresholds. An order identifier normally needs exact matching; do not silently replace a missing order with an unrelated nearby order. Lower-bracket matching is suitable only when the keys represent intentional thresholds.

=IF(H4<>"OK",H4,IF(NOT(ISNUMBER(E2)),"Enter numeric query",XLOOKUP(E2,C2:C6,D2:D6,"Missing Key",-1,-2)))

Audit order once, and understand the performance boundary

An order audit examines the whole key range and therefore has linear cost. Repeating that audit inside thousands of formulas can erase the benefit of a logarithmic search. Here H3 and H4 are shared audit cells; revalidate them whenever source keys change.

Excel’s binary search requires the appropriate sort order. Unsorted inputs can return invalid results without a useful error; the example blocks them before looking up. The five-row demonstration checks correctness, not million-row speed.

The current Arte Sheets formula engine accepts search modes 2 and −2 but implements them through a linear lookup path. These examples reproduce the intended results for unique sorted numeric keys; they do not establish binary-search acceleration in Arte Sheets.

Try deliberate errors

Change A4 to 1095: the ascending audit should block H6 and H8 while the separate descending outputs remain available. Restore A4 to 1040, then duplicate a neighboring key and observe the duplicate policy.

Try the first and last keys, a gap between keys and a query outside the range. Both directions should agree when they represent the same key-value pairs and matching rule.

Further reading

• Microsoft Support — XLOOKUP function