Lesson 6: INDEX/MATCH

SheetDrill Lesson 6 · INDEX/MATCHFR

Lesson 6 · Pro

fx

✓

Lesson 6 done.

You can find a position with MATCH, pull a value with INDEX, put them together to look up in any direction, cross a row and a column in a two-way lookup, and lock it all down to copy. INDEX/MATCH works in every version of Excel and Google Sheets.

Back to all lessons

What you're learning

MATCH tells you where something is: =MATCH(G2, B2:B11, 0) gives its position in the range (1, 2, 3…). The 0 means exact match; leave it out and Excel guesses. INDEX goes the other way: give it a position and it returns the value, =INDEX(E2:E11, 5), or a row and a column, =INDEX(A2:E11, 5, 3).

Together: =INDEX(E2:E11, MATCH(G2, B2:B11, 0)). MATCH finds the row, INDEX returns from any column, left or right. The two ranges must start on the same row and be the same size, or you land on the wrong row.