Two-way lookups

Find a price in a grid by both size and colour, using INDEX with two MATCHes. Advanced · 6 min · INDEX, MATCH, HLOOKUP, 2D lookup

Some answers depend on two inputs: a price set by size and colour, or a delivery rate by zone and weight. The data sits in a grid, with labels down the side and across the top.

INDEX can take a row number and a column number: =INDEX(B2:F6, 3, 4) returns the cell 3 rows down and 4 columns across. Find the row with one MATCH and the column with another, and you have a two-way lookup:

=INDEX(grid, MATCH(row_value, row_labels, 0), MATCH(column_value, column_labels, 0))

Steps

A customer wants a hoodie in the size in I2 and the colour in I3. In I4, use MATCH to find which row of the grid that size is on, searching the size labels in A2:A6.
In I5, find which column the colour is in, searching across the header row B1:F1.
In I6, fetch the price from the grid B2:F6 with INDEX, using both positions: =INDEX(B2:F6, I4, I5)
Helper cells are great while you build a formula, but you can nest everything. In I8, get the same price in one formula, with both MATCHes inside INDEX.
A side note: HLOOKUP is VLOOKUP turned on its side. It searches the top row of a table and returns from a row further down. In I10, find the size M price for the chosen colour: =HLOOKUP(I3, B1:F6, 3, FALSE)

Remember

INDEX(grid, row, column) returns one cell from a two-dimensional range.
One MATCH finds the row and another finds the column.
Keep the grid range (just the values) separate from the label ranges.
HLOOKUP searches across the top row, but INDEX/MATCH is more flexible.

Previous: XLOOKUP · Next: Joining text