Excel Practice
Lessons Lesson 41

INDEX and MATCH Together

About this lesson

MATCH finds which row a value is in. INDEX returns the value in that row from any column. Together they look up in any direction.

By the end you can

  • build a lookup from INDEX and MATCH that returns from any column, left or right.
  • find a row by one column and read a value from another.
  • find both the row and the column with two MATCHes.

Practises INDEX MATCH VLOOKUP XLOOKUP

The idea

It is more typing than VLOOKUP, and it is worth it for two reasons. It looks left: the column you search and the column you return are named separately. So the answer does not have to sit to the right of the question. And it survives an inserted column.

VLOOKUP's third argument is a count. 3 means "the third column of that range". Insert a new column in the middle and the 3 still says 3, but the data under it has moved one place. The formula does not break. It just returns the wrong column. INDEX and MATCH name their columns as ranges, and a range moves when a column is inserted before it. The formula that stays right when somebody else edits the sheet is the one to write.

The mistake to watch for

Ranges that start on different rows. MATCH counts from the top of the range it searched. INDEX reads from the top of the range it was given. So the two must begin on the same row, or every answer is the one above or below. Check both ranges before you check the data.

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

Build it in two halves. MATCH finds which row a name sits in.

In E2, type =MATCH(F2,B2:B5,0): F2 holds a name, B2:B5 the names, 0 exact.

E2
Row A B C D E F G
1 Payroll no Name Department Annual salary
2 4102 H. Okafor Design 41200 T. Alvarez
3 4118 M. Lindqvist Support 33500
4 4126 T. Alvarez Design 46800
5 4139 P. Nwosu Finance 52400 P. Nwosu
6
7
8

Every step

  1. 1

    Build it in two halves. MATCH finds which row a name sits in. In E2, type =MATCH(F2,B2:B5,0): F2 holds a name, B2:B5 the names, 0 exact.

    Hint. F2, B2:B5, 0.

  2. 2

    Now the other half. INDEX fetches from a position. Read =INDEX(D2:D5,3) and say what it returns: the salaries, third one down.

    Hint. The third salary.

  3. 3

    Put them together. In E4, write INDEX over the salaries D2:D5. Instead of a typed 3, use the MATCH from step 1 as the position. Then the salary follows the name in F2.

    Hint. INDEX outside, MATCH inside.

  4. 4

    Here is what VLOOKUP cannot do at all. F5 holds a name. In E5, get that person's payroll number. It sits to the left of the names. Use INDEX over A2:A5, with MATCH searching B2:B5 for F5.

    Hint. INDEX over column A.

  5. 5

    In E6, write a formula with INDEX and MATCH that gives the name of the person in the Finance department. Search the departments and return the names.

    Hint. Search C, return B.

  6. 6

    INDEX can take a row and a column, so a MATCH can go in each. In E7, look in the whole table A1:D5 for M. Lindqvist's department. One MATCH finds the row by name in B1:B5. The other finds the column by the heading Department in A1:D1.

    Hint. One MATCH for the row, one for the column.