Spreadsheet Practice

Lesson 11 of 29, Lookups

INDEX & MATCH

Look up by row and column at the same time.

Pro10 minutes, 2 exercisesINDEXMATCH

Watch it done

3 steps

Watch the technique worked out on sample data, then try it yourself in the exercises.

How it works

MATCH(value, list, 0) returns the position of a value in a list: Zone 3 is in position 3. INDEX(range, row, column) returns whatever sits at that position.

Use one MATCH for the row and another for the column, and you have a two-way lookup that works in every version of Excel.

Syntax

=MATCH(H2,A2:A6,0)Position of the zone
=INDEX(B2:E6,3,3)Row 3, column 3 of the grid
=INDEX(B2:E6,MATCH(H2,A2:A6,0),MATCH(H3,B1:E1,0))Two-way lookup

Watch out for

  • MATCH needs 0 as its last argument for an exact match; without it, it assumes sorted data.
  • INDEX counts from the first cell of its own range, not from row 1 of the sheet.

The exercises

  1. Find the position. Next to "Zone position", in H5, return the position of the zone from H2 within A2:A6.
  2. Two-way lookup. Next to "Rate", in H6, return the shipping rate for the zone in H2 and the weight in H3.

Graded practice for this lesson is part of Pro

The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 27 Pro lessons, Excel and Google Sheets, 79 exercises in all.

  1. Find the position
  2. Two-way lookup
Try these functions in the Formula Lab