# How to do a two-way lookup with an approximate match in Excel

Use an approximate MATCH for the banded side and an exact one for the other: `=INDEX(B2:D6,MATCH(G1,A2:A6,1),MATCH(G2,B1:D1,0))`.

**Excel:** `=INDEX(B2:D6,MATCH(G1,A2:A6,1),MATCH(G2,B1:D1,0))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Qty from | Matte | Gloss | Linen |  | Quantity | 650 |
| 1 | 0.42 | 0.48 | 0.61 |  | Paper | Gloss |
| 100 | 0.35 | 0.4 | 0.52 |  | Price each |  |
| 250 | 0.29 | 0.33 | 0.44 |  |  |  |
| 500 | 0.24 | 0.27 | 0.37 |  |  |  |
| 1000 | 0.19 | 0.22 | 0.31 |  |  |  |

Formula in G3: `=INDEX(B2:D6,MATCH(G1,A2:A6,1),MATCH(G2,B1:D1,0))`

Result: $0.27

## How it works

The quantity side is banded: `MATCH(G1,A2:A6,1)` returns the last break at or below 650, the 500 row. The paper side is exact: `MATCH(G2,B1:D1,0)` finds Gloss. INDEX returns the price where that row and column meet, $0.27.

## Other ways

- `=XLOOKUP(G1,A2:A6,XLOOKUP(G2,B1:D1,B2:D6),,-1)`: The inner XLOOKUP picks the Gloss column; the outer one uses match mode -1 for the quantity break. The breaks don't need sorting.
- `=VLOOKUP(G1,A2:D6,MATCH(G2,A1:D1,0),TRUE)`: VLOOKUP with TRUE for the bands and MATCH for the column number. Works in every version.

## Common mistakes

- MATCH with 1 and VLOOKUP with TRUE need the quantity breaks sorted from smallest to largest.
- Using 0 on the quantity side returns #N/A for any quantity that isn't exactly a break.
- A quantity below the first break returns #N/A. Start the table at the smallest possible order.

## Questions

**How do I get the order total?**

Multiply by the quantity: `=G1*INDEX(B2:D6,MATCH(G1,A2:A6,1),MATCH(G2,B1:D1,0))`.

**What if both sides are banded?**

Use 1 in both MATCHes, with both header ranges sorted ascending.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/two-way-approximate-lookup