Syntax
=INDEX(reference, [row], [column])
reference- The range to pick from.
row- Optional. Row number within the range (1 = first row of the range).
column- Optional. Column number within the range.
INDEX function in Google Sheets
INDEX returns the value at a given row and column position inside a range. On its own it's simple. Paired with MATCH, it becomes the most flexible lookup in Google Sheets.
Practice INDEX
Task 1 of 4
Use INDEX to return the song in 4th place from the list in B2:B11. Answer in D2.
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 examples
Each example below runs against this sample sheet. The results are calculated by the same engine as the practice grid.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Emp ID | Name | Dept | Salary | |
| 2 | 1042 | Maya Patel | Finance | maya.patel@acme.co | $72,000.00 |
| 3 | 1187 | Leo Rossi | Support | leo.rossi@acme.co | $51,000.00 |
| 4 | 1305 | Ava Kim | Sales | ava.kim@acme.co | $64,000.00 |
| 5 | 1411 | Omar Ali | Marketing | omar.ali@acme.co | $58,000.00 |
| 6 | 1520 | Hana Tanaka | Sales | hana.tanaka@acme.co | $69,000.00 |
| Formula | Result | What it shows |
|---|---|---|
=INDEX(B2:B6, 3) | Ava Kim | Third name in the list. |
=INDEX(A2:E6, 2, 5) | $51,000.00 | Row 2, column 5 of the table: Leo's salary. |
=INDEX(A1:E1, 4) | With a single row, the number picks a column. | |
=INDEX(B2:B6, MATCH(1411, A2:A6, 0)) | Omar Ali | INDEX MATCH: name for ID 1411. |
Tips and common mistakes
- Positions are relative to the range, not the sheet. Row 1 of B2:B6 is row 2 of the sheet.
- A position outside the range returns #REF!.
Related functions
Learning step by step? INDEX is covered in Lesson 19: INDEX Function of the free course.