Why is XMATCH + INDEX useful for large approximate lookups?

XMATCH uses binary search in a sorted column to find boundaries in O(log n), while INDEX returns values from an independent return range, making it safe when columns move.

How do I choose lower or upper boundaries?

In XMATCH, match_mode -1 returns an exact match or the next smaller value (lower bound), while 1 returns an exact match or the next larger value (upper bound). Document your policy and apply it consistently.

Which item should I choose when boundaries are duplicated?

You can use search_mode to force the first or last occurrence. The safest approach is to remove duplicates from the table.

Values fluctuate because of decimal boundaries or units. What is the solution?

Snap values to bucket units with FLOOR.MATH or CEILING.MATH before applying XMATCH to reduce fluctuations.

How can I maintain speed with 1 million rows?

Reduce calculation volume and file size by guaranteeing sorting, minimizing ranges, caching with helper columns, reusing spill formulas, and saving as .xlsb.

CONTENTS

Build Fast Approximate Lookups with XMATCH + INDEX, Even for 1 Million Rows (Sorting and Binary Search)

The XMATCH INDEX approximate lookup combination is fast and transparent, even with large datasets. Use XMATCH binary search to identify the boundary index accurately, then retrieve the value with INDEX to eliminate VLOOKUP’s weaknesses.

Quick Fix — 6 Steps to Fix “Slow” or “Incorrect” Results

Confirm that the lookup column is sorted in ascending order.
Choose -1 for the lower bound and 1 for the upper bound.
Use a separate INDEX(XMATCH()) design.
Lock ranges with Excel tables or absolute references.
Snap to buckets with FLOOR.MATH/CEILING.MATH.
Set a duplicate-boundary policy and apply search_mode.

Why XMATCH + INDEX?

Binary Search-Based match_mode

=XMATCH(value, lookup_range, -1)  // exact match or next smaller (≤)
=XMATCH(value, lookup_range,  1)  // exact match or next larger (≥)

Benefits of a Separate Design

Because INDEX uses an independent return range, it remains stable when columns move. Separating the XMATCH result into a helper column also makes testing easier.

Approximate Lookup Patterns

Lower-Bound Lookup

=INDEX($B$2:$B$6, XMATCH(E2, $A$2:$A$6, -1))

Upper-Bound Lookup

=INDEX($B$2:$B$6, XMATCH(E2, $A$2:$A$6, 1))

Duplicate Boundary Priority

=INDEX($B$2:$B$6, XMATCH(E2, $A$2:$A$6, -1, -1))

Performance Design for 1 Million Rows

Guarantee sorting: standardize number and date formats, and use Excel tables.
Minimize ranges, cache with helper columns, and eliminate repeated calculations with spills.
Reduce file size by saving as .xlsb.

Practical Example 1 — Score to Grade

Minimum Score	Grade
0	D
60	C
70	B
80	A
90	A+

=INDEX($B$2:$B$6, XMATCH(E2, $A$2:$A$6, -1))

Boundary snap: =INDEX($B$2:$B$6, XMATCH(FLOOR.MATH(E2,1), $A$2:$A$6, -1))

Practical Example 2 — Usage to Unit Price (Tiered Pricing)

Minimum Usage	Unit Price (KRW)
0	100
100	90
300	80
500	70

Unit price lookup: =INDEX($B$2:$B$5, XMATCH(G2, $A$2:$A$5, -1))

Tiered total: After calculating usage for each tier, use =SUMPRODUCT(usage_range_by_tier, unit_price_range)

Alternatives, Considerations, and Checklist

INDEX+XMATCH is easier to debug than an all-in-one XLOOKUP formula.
Handle exceptions with bucket snapping and virtual lower or upper bounds.

[ ] Sorting guaranteed
[ ] Data types standardized
[ ] Intent for -1/1 specified
[ ] Duplicate policy and search direction set
[ ] Bucket snapping applied
[ ] Out-of-range exceptions handled

Conclusion & Related Articles

Official documentation: XMATCH Function, INDEX Function

근사 모드 VLOOKUP vs XLOOKUP 근사 — 경계값 함정 완벽 정리

XMATCH INDEX Approximate Lookup: Fast Even with 1 Million Rows (Sorting and Binary Search)