INDEX Function in Google Sheets
Returns the value of a cell at a specified row and column within a range. Paired with MATCH for the classic INDEX/MATCH lookup pattern that predates XLOOKUP and remains widely used.
Syntax
INDEX(reference, [row], [column])Returns: The value at the specified position, or a full row/column array if row or column is 0.
Excel equivalent: INDEX (identical)
Parameters
| Name | Required | Description |
|---|---|---|
| reference | Required | The range or array to index into. |
| row | Optional | The row offset within reference (1-based). Pass 0 to return the whole column. |
| column | Optional | The column offset within reference (1-based). Pass 0 to return the whole row. |
Examples
Get the 5th row, 2nd column of a range
=INDEX(A1:C100, 5, 2)Returns the value in B5. Useful when you have positional data you want to slice.
INDEX + MATCH for a left-of-lookup-column return
=INDEX(Products!A:A, MATCH("SKU-123", Products!C:C, 0))Search column C for "SKU-123", then return the matching value from column A — something VLOOKUP can't do because it only returns columns to the right of the search.
Return an entire column as a spilled array
=INDEX(Sales!A:E, 0, 3)row = 0 means "all rows", so this returns the entire column C from the Sales sheet. Combine with FILTER for dynamic column extraction.
When to use an alternative
- XLOOKUP — You need a single-cell lookup with a not-found fallback — XLOOKUP is cleaner.
- OFFSET — You need to return a range (not just a value) of a specific size starting from an anchor.
- CHOOSE — You only have a handful of fixed positions to pick from.
Common errors and how to fix them
#REF!
Cause: row or column exceeds the dimensions of reference.
Fix: Check that your row/column arguments are within the bounds of the range.
#N/A from MATCH inside
Cause: MATCH didn't find the search value, so INDEX received #N/A as its row.
Fix: Wrap INDEX(...MATCH...) with IFERROR or use XLOOKUP which handles this natively.
Related functions
Frequently Asked Questions
Why use INDEX + MATCH instead of VLOOKUP?
Three reasons: (1) INDEX/MATCH can look up values to the left of the search column (VLOOKUP can only go right), (2) inserting a column in your range doesn't break the formula (VLOOKUP's hardcoded index breaks), (3) it's faster on very large datasets because it only scans one column instead of all columns up to the index.
Can INDEX return a range instead of a single value?
Yes. Pass 0 (or omit) for either row or column to return that entire dimension. INDEX(A1:E100, 0, 3) returns the entire third column of the range as a spilled array.