Lesson 21 · Lookups

INDEX MATCH

Master INDEX MATCH in Google Sheets: flexible lookups in any direction and two-way row/column lookups.

Task 1 of 2

Return the name for the employee ID in E2 using INDEX and MATCH. Answer in F2.

Tip: while typing a formula, click a cell or drag across cells to insert references. Press Enter to check, and Tab to accept a function suggestion.

INDEX + MATCH: the flexible lookup

Put MATCH inside INDEX: MATCH finds the row number, and INDEX returns the value at that row from whichever column you choose.

=INDEX(return_range, MATCH(search_key, lookup_range, 0))

Because the return range and lookup range are separate, you can look left, right, up or down. Inserting a column won't break the formula, unlike VLOOKUP's hard-coded column number.

Two-way lookup

Use one MATCH for the row and another for the column:

=INDEX(B2:E7, MATCH(G2, A2:A7, 0), MATCH(H2, B1:E1, 0))