What Is the INDEX Function?
INDEX returns the content of a cell at a specific row and column position within a given range. You tell it which range to look in, which row number, and optionally which column number, and it hands back the value sitting at that intersection. On its own, INDEX is straightforward, but when combined with MATCH it becomes one of the most versatile lookup tools available in Google Sheets.
Syntax
=INDEX(reference, [row], [column])
| Parameter | Description |
|---|---|
| reference | The range of cells to retrieve data from. |
| row | The row position within the range (1-based). Optional if the range is a single row. |
| column | The column position within the range (1-based). Optional if the range is a single column. |
If you pass 0 for the row or column, INDEX returns the entire column or row respectively. This is useful inside other functions like SUMPRODUCT.
Basic Examples
Retrieving a Value from a Single Column
| A | |
|---|---|
| 1 | January |
| 2 | February |
| 3 | March |
| 4 | April |
=INDEX(A1:A4, 3)
Result: March. The third value in the range.
Retrieving from a Table by Row and Column
| A | B | C | |
|---|---|---|---|
| 1 | Name | Department | Salary |
| 2 | Ava | Engineering | 95000 |
| 3 | Ben | Marketing | 72000 |
| 4 | Clara | Finance | 88000 |
=INDEX(A2:C4, 2, 3)
Result: 72000. Row 2 of the range is Ben's row, column 3 is Salary.
Pulling the Last Value in a Column
When new data is appended regularly, you might want the latest entry:
=INDEX(A:A, COUNTA(A:A))
COUNTA counts all non-empty cells in column A, and INDEX uses that count to jump to the last filled row.
Advanced Examples
INDEX-MATCH for Left Lookups
VLOOKUP cannot look to the left of the search column, but INDEX-MATCH can. Suppose you know an employee ID in column C and want their name from column A:
| A | B | C | |
|---|---|---|---|
| 1 | Name | Department | Employee ID |
| 2 | Ava | Engineering | E-101 |
| 3 | Ben | Marketing | E-102 |
| 4 | Clara | Finance | E-103 |
=INDEX(A2:A4, MATCH("E-102", C2:C4, 0))
Result: Ben. MATCH finds the position of "E-102" in the ID column, and INDEX pulls the corresponding name.
Two-Dimensional INDEX-MATCH
You can use MATCH for both the row and column arguments to build a fully dynamic lookup:
=INDEX(B2:D4, MATCH(G1, A2:A4, 0), MATCH(H1, B1:D1, 0))
Cell G1 holds the row lookup key (e.g., "Clara"), H1 holds the column header (e.g., "Salary"). The formula finds both positions dynamically and returns the intersecting value. This pattern is ideal for dashboards where users select criteria from dropdowns.
Returning an Entire Row for SUMPRODUCT
=SUMPRODUCT(INDEX(B2:B100, 0) * INDEX(C2:C100, 0))
Passing 0 as the row makes INDEX return the entire column, which SUMPRODUCT can then multiply element-by-element. This calculates the sum of quantity times price across all rows.
Common Mistakes
- Mixing up row and column arguments. INDEX uses (row, column) order within the range. If your range is A2:C4, row 1 is A2:C2, not A1:C1. The position is relative to the range, not the worksheet.
- Using INDEX alone when you need a dynamic row. Hardcoding
=INDEX(A1:A10, 3)is fine for static references, but for real lookups you almost always want MATCH to supply the row number dynamically. - Forgetting that INDEX is 1-based. The first row of your range is position 1, not 0. Passing 0 does not mean "first" -- it means "return the entire row or column."
SheetAI Tip
Don't want to memorize this syntax? With SheetAI, just type what you need in plain English and the formula is generated for you. Install SheetAI to try it free.