Excel Practice
Lessons Lesson 45

Two-Way Lookup: INDEX with Two MATCHes

About this lesson

A two-way lookup finds a value by a row label and a column label at once. INDEX over the block, with one MATCH supplying the row number and another the column number.

By the end you can

  • look up a value by a row label and a column label together.
  • say why the INDEX block must start where the labels start.

Practises INDEX MATCH XLOOKUP VLOOKUP

The idea

A price grid, a rota, a distance table. Anything with labels down the side and across the top is a two-way lookup. VLOOKUP cannot do it, because its column number is typed. INDEX takes a row and a column. MATCH finds each from its label. So INDEX(block, MATCH(row label), MATCH(column label)) reads the crossing point.

The mistake is the block. INDEX counts rows and columns inside the range you give it. So the block must start where the labels start: D2:F4 with C2:C4 and D1:F1, not C1:F4. Off by one row, and every answer is the one above it, with no error to say so.

The mistake to watch for

A block that starts one row or column away from where the labels start. INDEX counts inside the range it is given. So D2:F4 goes with C2:C4 and D1:F1. Give it C1:F4 and every answer is the one above or beside the right one, with nothing to say so. Check the opposite corner of the grid, not the first cell.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

In B5, find which row of the product list C2:C4 holds the product in B2, with MATCH.

To begin, type it exactly:

=MATCH(B2,C2:C4,0)

B5
Row A B C D E F G
1 Want Product S M L
2 Product Hoodie Tee 8 9 10
3 Size L Hoodie 22 24 26
4 Price Cap 12 12 14
5 Row
6 Column
7
8 Cap in M

Every step

  1. 1

    In B5, find which row of the product list C2:C4 holds the product in B2, with MATCH. To begin, type it exactly: =MATCH(B2,C2:C4,0).

    Hint. Down the product column.

  2. 2

    In B6, find which column of the size headings D1:F1 holds the size in B3.

    Hint. Across the heading row.

  3. 3

    In B4, the price itself. INDEX over the block of prices D2:F4, with the two MATCHes supplying the row and the column.

    Hint. INDEX, then two MATCHes.

  4. 4

    With the positions typed in directly, read =INDEX(D2:F4,3,1) and say what it returns.

    Hint. Cap, size S.

  5. 5

    In B8, write a formula using INDEX and MATCH that gives the price of a Cap in size M. Type both labels into the formula as text.

    Hint. Two MATCHes with text in quotation marks.