Two-Way Approximate Lookup (Rate Grid)

Excel Formulas › Lookup

All versionsINDEXMATCH

Shipping costs by weight and zone, a tax rate by income and status, pricing by quantity and tier — these need a two-way lookup that lands in the right band on both axes. INDEX with two approximate MATCHes does it on a sorted grid.

Quick formula: for a value V1 down the side and V2 across the top of a sorted grid:
=INDEX(rates, MATCH(V1, rowBreaks, 1), MATCH(V2, colBreaks, 1))
MATCH(…, 1) finds the largest breakpoint not exceeding the value — the correct band — on each axis.

Functions used (tap for the full reference guide):

INDEXMATCH

The example

Shipping cost by weight (rows) and zone breakpoints (cols). Look up 7 lb, zone-distance 250.

ABCD
1Wt \ Dist0200400
20$5$7$9
35$8$11$14
410$12$16$20

The formula

The rate for 7 lb at distance 250 (falls in the 5-lb row, 200-distance column):

=INDEX(B2:D4, MATCH(7, A2:A4, 1), MATCH(250, B1:D1, 1)) // 5-lb band × 200-band → $11

How it works

Approximate MATCH on each axis finds the band:

  1. MATCH(7, A2:A4, 1) with type 1 finds the largest weight breakpoint not over 7 — the 5-lb row (position 2).
  2. MATCH(250, B1:D1, 1) does the same across the top — the 200 column (position 2).
  3. INDEX(B2:D4, 2, 2) returns the cell at that row/column band — $11.
  4. Both axes must be sorted ascending for approximate MATCH to band correctly.

Sorting is mandatory here. Approximate MATCH (type 1) assumes the breakpoints ascend. On unsorted axes it returns the wrong band with no error — the classic silent mistake. Sort both the row and column headers.

Try it: interactive demo

Live demo

Set a weight and distance; see which band the grid lands on.

Rate:

Variations

Exact two-way (labels)

Use MATCH type 0 for exact row/column labels:

=INDEX(rates, MATCH(V1,rows,0), MATCH(V2,cols,0))

Nested XLOOKUP approximate

Excel 365, match_mode -1 on each axis:

=XLOOKUP(V1, rows, XLOOKUP(V2, cols, rates, , -1), , -1)

One axis exact, one approximate

Mix MATCH types — e.g. exact zone, banded weight.

Pitfalls & errors

Unsorted axes = wrong band, no error. Approximate MATCH needs both header axes sorted ascending. This is the #1 cause of silently wrong rates.

Below the first breakpoint. A value smaller than the first row/column breakpoint returns #N/A. Start each axis at 0 (or the minimum) to cover everything.

Breakpoints are “at least.” Each band means “this breakpoint up to the next.” Make sure your grid’s headers represent the lower edge of each band.

Practice workbook

📊
Download the free Two-Way Approximate Lookup (Rate Grid) practice workbook
A sorted rate grid with live two-way approximate INDEX/MATCH, the exact and XLOOKUP variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I do a two-way lookup that finds the right band on both axes?
Use INDEX with two approximate MATCHes: =INDEX(grid, MATCH(v1, rowBreaks, 1), MATCH(v2, colBreaks, 1)). Type 1 finds the largest breakpoint not exceeding each value. Both axes must be sorted ascending.
Why is my rate-grid lookup returning the wrong value?
Approximate MATCH requires both the row and column breakpoints to be sorted ascending. Unsorted headers return the wrong band silently. Sort both axes.
How do I do this with XLOOKUP?
Nest XLOOKUP with match_mode -1 on each axis: =XLOOKUP(v1, rows, XLOOKUP(v2, cols, grid, , -1), , -1).

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Two-way lookup · Tax-bracket lookup · 2-D INDEX/MATCH/MATCH

Function references: INDEX · MATCH