Spreadsheet Practice

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

Formula (Excel and Google Sheets)

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

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)).

Result on the example
$0.27 in G3
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in G3. Change any input and the result updates, or edit the formula to experiment.

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.

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

Result0.27

  1. B2:D65 rows × 3 cols: $0.42, $0.48, $0.61, $0.35, …

    A range: 5 rows × 3 columns.

  2. G1650

    The value in G1.

  3. A2:A65 rows × 1 col: 1, 100, 250, 500, …

    A range: 5 cells in one column.

  4. MATCH(G1,A2:A6,1)4

    MATCH: Position of a value in a list. 0 = exact match.

    • lookup_valueG1650
    • lookup_arrayA2:A65 rows × 1 col: 1, 100, 250, 500, …
    • match_type1
  5. G2"Gloss"

    The value in G2.

  6. B1:D11 row × 3 cols: "Matte", "Gloss", "Linen"

    A range: 3 cells in one row.

  7. MATCH(G2,B1:D1,0)2

    MATCH: Position of a value in a list. 0 = exact match.

    • lookup_valueG2"Gloss"
    • lookup_arrayB1:D11 row × 3 cols: "Matte", "Gloss", "Linen"
    • match_type0
  8. INDEX(B2:D6,MATCH(G1,A2:A6,1),MATCH(G2,B1:D1,0))0.27

    INDEX: Value at a given row and column of a range.

    • arrayB2:D65 rows × 3 cols: $0.42, $0.48, $0.61, $0.35, …
    • row_numMATCH(G1,A2:A6,1)4
    • column_numMATCH(G2,B1:D1,0)2

In Google Sheets

The same formula works in Google Sheets: =INDEX(B2:D6,MATCH(G1,A2:A6,1),MATCH(G2,B1:D1,0)). We ran it in Google Sheets mode on the same data and it returns $0.27.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=XLOOKUP(G1,A2:A6,XLOOKUP(G2,B1:D1,B2:D6),,-1)$0.27The 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)$0.27VLOOKUP 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.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Do a two-way lookup with an approximate match

The price per card drops at quantity breaks. In G3, return the price each for the quantity in G1 on the paper in G2.

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.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.