How to Use INDEX in Google Sheets: Comprehensive Guide

INDEX returns the content of a cell inside a range, given a row and column position. On its own it is a simple "give me the 3rd item" function. Combined with MATCH, it becomes the most flexible lookup in Google Sheets, one that can look in any direction and does not break when columns are inserted.

Syntax

=INDEX(reference, [row], [column])

reference: The range to pick from.
row [Optional]: The row position within reference. Default is 0.
column [Optional]: The column position within reference. Default is 0.

Positions are counted inside the range, starting at 1, not by sheet row or column numbers. In INDEX(C5:E10, 2, 1), row 2 means the second row of the range (sheet row 6).

If row or column is 0 or omitted, INDEX returns the entire row or column, which is useful inside other functions.

Basic examples

With this data in A1:C4:

   A        B       C
1  Name     Dept    Salary
2  Ana      Sales   52000
3  Ben      IT      61000
4  Chloe    Sales   58000

=INDEX(A1:C4, 3, 1) returns Ben (3rd row, 1st column).
=INDEX(A1:C4, 4, 3) returns 58000.
=INDEX(C1:C4, 2) returns 52000 (a single column, so only a row is needed).
=INDEX(A1:C4, 2, 0) returns the entire 2nd row: Ana, Sales, 52000.
=INDEX(A1:C4, 0, 3) returns the entire 3rd column, which you can pass to SUM, MAX and others.

INDEX + MATCH: the flexible lookup

MATCH finds the position of a value in a list, and INDEX returns the value at a position in another list. Together they replace VLOOKUP:

=INDEX(C2:C100, MATCH("Ben", A2:A100, 0))

Step by step: MATCH("Ben", A2:A100, 0) returns 2, the position of Ben in the name column. INDEX(C2:C100, 2) returns the 2nd item of the salary column, which is 61000. The 0 in MATCH means exact match.

Why people prefer this to VLOOKUP:

It can look left. The lookup column and the result column are independent, so the key does not have to be the first column.
It survives inserted columns. You reference the result column directly instead of counting to it.
It defaults to exact matching when you pass 0. There is no silent approximate match.

Two-way lookup

Use two MATCH calls to find a value at the intersection of a row label and a column label:

=INDEX(B2:M20, MATCH("North", A2:A20, 0), MATCH("Mar", B1:M1, 0))

MATCH("North", A2:A20, 0) gives the row, MATCH("Mar", B1:M1, 0) gives the column, and INDEX returns the cell where they cross. This is the standard way to read a value out of a report with labels on both axes.

More patterns

Last value in a column

=INDEX(A:A, COUNTA(A:A))

COUNTA counts the filled cells and INDEX returns the item at that position. This works when the column has no blank gaps.

Every n-th value

=INDEX(A2:A100, SEQUENCE(10, 1, 1, 3))

Returns the 1st, 4th, 7th, ... items, because SEQUENCE produces the positions 1, 4, 7, ... and INDEX accepts an array of positions.

Pick from a list by number

=INDEX({"Low", "Medium", "High"}, B2)

Turns a rating of 1, 2 or 3 into a label, without a long nested IF.

Case-sensitive lookup

MATCH ignores case. Combine INDEX with MATCH and EXACT to respect it:

=INDEX(B2:B100, MATCH(TRUE, ARRAYFORMULA(EXACT(A2:A100, E2)), 0))

Common errors

#REF!. The row or column is outside the range. INDEX(A1:C4, 5, 1) fails because the range only has 4 rows.
#N/A from INDEX/MATCH. The MATCH found nothing. Check for extra spaces (TRIM) and text-versus-number mismatches, and make sure you passed 0 as the match type.
Off-by-one results. MATCH and INDEX must use ranges that start on the same row. If MATCH searches A2:A100 but INDEX returns from C1:C100, every result is shifted by one.

INDEX vs. VLOOKUP vs. XLOOKUP

INDEX/MATCH and XLOOKUP cover the same ground. XLOOKUP is shorter and has a built-in "not found" value. INDEX/MATCH works in every spreadsheet tool and is useful when you need the position itself or want to use INDEX for something other than a lookup. VLOOKUP is the simplest but the least flexible. See the full comparison in Use INDEX-MATCH Instead of VLOOKUP.

Related Functions

MATCH: Finds the position of a value in a range, the partner of INDEX.
VLOOKUP: Vertical lookup by column number.
HLOOKUP: Horizontal lookup by row number.
XLOOKUP: A modern single-function lookup.
INDIRECT: Returns a cell reference specified by a text string.

INDEX