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

By Ali, Founder, Excel Lessons

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.

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

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. That is the salaries, third one down.

    Hint. The third salary.

  3. 3

    Put them together. In E4, find the salary for F2's name. Shape: =INDEX(range,MATCH(lookup,range,0)). MATCH's answer becomes INDEX's position.

    Hint. INDEX outside, MATCH inside.

  4. 4

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

    Hint. INDEX over column A.

  5. 5

    In E6, find the name of the person in Finance. Search the departments, 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, find B3's department in the table A1:D5. Shape: =INDEX(table,MATCH(value,range,0),MATCH(value,range,0)). One MATCH finds the row, the other the column.

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