Spreadsheet Practice

Lesson 14 of 45, Lookups

INDEX & MATCH

Quick answer

Look up by row and column at the same time.

Key formula
=MATCH(H2,A2:A6,0) (Position of the zone)
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.

Questions people ask about this

Updated Checked by running: every exercise answer on this page was graded by our formula engine on .

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 43 Pro lessons, Excel and Google Sheets, 125 exercises in all.

  1. Find the position
  2. Two-way lookup