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

By Ali, Founder, Excel Lessons

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.

Read more about this

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

MATCH finds which row of a list holds a value.

In B5, type:

=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

    MATCH finds which row of a list holds a value. In B5, type: =MATCH(B2,C2:C4,0).

    Hint. Down the product column.

  2. 2

    In B6, find which column of D1:F1 holds B3's size.

    Hint. Across the heading row.

  3. 3

    In B4, find the price with INDEX and two MATCHes. Shape: =INDEX(range,MATCH(lookup,range,0),MATCH(lookup,range,0)). One MATCH gives the row, the other 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, find the price of a Cap in size M. Type both labels as quoted text.

    Hint. Two MATCHes with text in quotation marks.