INDEX Function in Excel, Google Sheets, Airtable & Notion
INDEX returns the value at a given position in a range: INDEX(A2:B6, 3, 2) is the value in the third row and second column of A2:B6. On its own it is simple; combined with MATCH it becomes a flexible lookup.
Syntax in each app
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | INDEX(array, row_num, [column_num])There is also a reference form, INDEX(reference, row_num, [column_num], [area_num]), for several areas. |
| Google Sheets | ✔ Built in | INDEX(reference, [row], [column]) |
| Airtable | ✘ Not available | — Airtable formulas cannot address other rows. To pick the nth linked value, use a rollup field with ARRAYSLICE(values, 2, 2) (1-based), or a lookup field. |
| Notion | ≈ Workaround | list.at(index).at() is 0-based, so the 3rd item is .at(2). Works on lists and relation properties, e.g. prop("Students").at(0). |
| Excel | =INDEX(A2:B6, 3, 2) | open excel.new ↗ |
|---|---|---|
| Google Sheets | =INDEX(A2:B6, 3, 2) | open sheets.new ↗ |
| Airtable | ✘ No equivalent. Airtable formulas cannot address other rows. To pick the nth linked value, use a rollup field with ARRAYSLICE(values, 2, 2) (1-based), or a lookup field. | |
| Notion workaround | [92, 75, 88, 64, 81].at(2) | |
Excel & Sheets use cell ranges. Paste this sample data into cell A1 of a blank sheet, then paste the formula into any empty cell.
Name Score Ann 92 Ben 75 Cara 88 Dev 64 Eli 81
The Notion formula picks from the Names list when column_num is 1 and from the Scores list otherwise.
Arguments
| Argument | What it means |
|---|---|
array | The range or array to pick from. |
row_num | Row position, starting at 1. 0 returns the whole column. |
[column_num] optional | Column position, starting at 1. 0 returns the whole row. Can be omitted for a single-column range. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=INDEX(A2:B6, 3, 2) | 88 | Tester data: row 3 of the block is Cara, column 2 is her score. | |
=INDEX(A2:A6, 4) | Dev | One column: the column number can be left out. | |
=INDEX({10, 20, 30}, 2) | 20 | Works on array constants too. | |
=INDEX(A2:B6, 0, 2) | 92, 75, 88, 64, 81 | Row 0 returns the whole second column (it spills). |
Examples use Excel / Google Sheets syntax unless noted.
How INDEX works
INDEX answers “what is in position n?”. That sounds trivial, but because the row and column can be calculated, it is the building block behind flexible lookups (INDEX + MATCH), “last value in a column” formulas such as =INDEX(A:A, COUNTA(A:A)), and picking the nth item from a list.
Positions are counted from the top-left of the range you give, not from row 1 of the sheet: in INDEX(A5:A20, 1) position 1 is A5. A row or column number beyond the range returns #REF!, and a negative one returns #VALUE!. A row of 0 returns the entire column and a column of 0 the entire row; in Excel 365 and Google Sheets the result spills into neighbouring cells. Empty cells come back as 0.
Airtable formulas cannot reach other rows, so there is no INDEX; in a rollup field ARRAYSLICE(values, n, n) picks the nth linked value. Notion lists have .at(), which counts from 0, plus .first() and .last().
Frequently Asked Questions
- How do I get the last value in a column?
- Use
=INDEX(A:A, COUNTA(A:A))if the column has no gaps, or=XLOOKUP(TRUE, A:A<>"", A:A, , 0, -1)in Excel 365. - Why does INDEX return #REF!?
- The row or column number is larger than the range. Remember positions are counted inside the range, not on the sheet.