Two-Way Lookup with INDEX and MATCH

Excel & Google Sheets

How it works

INDEX returns the value at a given row and column of a range — the trick is letting two MATCH functions supply those coordinates. The first MATCH finds your row label in the side column (Monitor is the 3rd row), the second finds your column header in the top row (Standard is the 2nd column), and INDEX grabs the cell where they cross. The 0 in each MATCH forces an exact match, so a typo returns #N/A instead of a silently wrong price. Because both lookups are dynamic, changing either dropdown cell instantly repoints the whole formula.

B2:D5

The grid of values only — no row labels, no headers.

MATCH(F2,A2:A5,0)

Finds which ROW your label sits in. Monitor → 3.

MATCH(G2,B1:D1,0)

Finds which COLUMN your header sits in. Standard → 2.

0

Exact match. Always use it — the default approximate mode misfires on unsorted data.

When to use it

Use it on any matrix-shaped table: price by product and tier, shipping cost by zone and weight, staffing by team and month — anywhere the answer lives at a row/column intersection.

Common mistakes

• ✕Including the labels and headers inside the INDEX range.

  ✓If INDEX covers A1:D5 but MATCH counts from A2 and B1, everything shifts by one. Keep INDEX on the values only (B2:D5) and point each MATCH at the matching labels.

• ✕Swapping the two MATCH functions.

  ✓INDEX takes row first, then column. If the row MATCH scans the header row, you get #REF! or the wrong cell. Row lookup scans the side column; column lookup scans the top row.

• ✕Omitting the 0 in MATCH.

  ✓Without it, MATCH assumes sorted data and returns the nearest position. Always write MATCH(value,range,0).