# INDEX function in Excel

> INDEX returns the value at a given row and column of a range: INDEX(A2:C10,4,2) is the fourth row, second column.

Source: https://spreadsheetpractice.com/functions/index · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** INDEX returns the value at a given row and column of a range: INDEX(A2:C10,4,2) is the fourth row, second column.
- **Syntax:** `=INDEX(array, row_num, [column_num])`
- **Most common use:** `=INDEX(A2:D5,3,4)` returns 498 (Row 3, column 4 of the table: Columbus in Q2)
- **Available in:** Every current version
- **Category:** [Lookup functions](https://spreadsheetpractice.com/functions/category/lookup)
- **Returns:** The value at that position, or a whole column or row when row_num or column_num is 0.
- **Google Sheets:** Works the same way in Google Sheets.

## What it does

INDEX returns the value at a given row and column of a range: INDEX(A2:C10,4,2) is the fourth row, second column. Paired with MATCH, which finds the row, it is the most flexible lookup in Excel: it works in every version, looks in any direction and doesn't break when columns move.

## Syntax

`=INDEX(array, row_num, [column_num])`

| Argument | Required? | What it is |
|---|---|---|
| `array` | Required | The range to pick from. |
| `row_num` | Required | The row within the range, counting from 1. Use 0 to return the whole column. |
| `column_num` | Optional | The column within the range, counting from 1. Leave it out for a single-column range. |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| F2 | `=INDEX(A2:D5,3,4)` | 498 | Row 3, column 4 of the table: Columbus in Q2 |
| F3 | `=INDEX(C2:C5,MATCH("Boise",A2:A5,0))` | 230 | With MATCH: Boise's Q1 figure |
| F4 | `=INDEX(A2:A5,MATCH(MAX(D2:D5),D2:D5,0))` | Columbus | Which city had the highest Q2, found by value instead of name |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | City | Region | Q1 | Q2 |  | Result |
| 2 | Austin | South | 410 | 455 |  | 498 |
| 3 | Boise | West | 230 | 262 |  | 230 |
| 4 | Columbus | Midwest | 515 | 498 |  | Columbus |
| 5 | Durham | South | 305 | 341 |  |  |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| #REF! | row_num or column_num is outside the range. INDEX(A2:D5,5,1) fails because the range has only four rows. |
| Off-by-one results | Positions count from the first cell of the range, not from row 1 of the sheet. If the range starts at row 2, row 1 of the range is sheet row 2. |
| INDEX and MATCH ranges don't line up | Both ranges must start on the same row. INDEX(C2:C5,MATCH(x,A3:A6,0)) returns the row below the one you want. |

## Tips

- Two-way lookup: =INDEX(C2:D5,MATCH("Austin",A2:A5,0),MATCH("Q2",C1:D1,0)).
- =SUM(INDEX(C2:D5,0,2)) totals the second column, because row_num 0 means every row.

## Related functions

- [MATCH](https://spreadsheetpractice.com/functions/match): MATCH returns the position of a value in a row or column: 3 means the third cell.
- [XLOOKUP](https://spreadsheetpractice.com/functions/xlookup): XLOOKUP searches one column or row for a value and returns the matching item from another.
- [XMATCH](https://spreadsheetpractice.com/functions/xmatch): XMATCH is the modern MATCH: it returns the position of a value in a list, but it defaults to an exact match, can search from the bottom up, and handles wildcards and next-larger or next-smaller matches without sorted data.
- [VLOOKUP](https://spreadsheetpractice.com/functions/vlookup): VLOOKUP looks for a value in the first column of a table and returns a value from another column in the same row.
- Comparison: [XLOOKUP vs INDEX and MATCH](https://spreadsheetpractice.com/functions/compare/xlookup-vs-index-match)

## Questions

### Why use INDEX MATCH instead of VLOOKUP?

It can return columns to the left of the search column, it doesn't depend on a column number that breaks when columns move, and it works in every version of Excel.

### Is INDEX MATCH better than XLOOKUP?

XLOOKUP is simpler to write. INDEX/MATCH is the better choice when the file must work in Excel 2019 or earlier, and it handles two-way lookups by row and column neatly.

## Practice

A graded INDEX exercise ("Two-way lookup") is on [the INDEX page](https://spreadsheetpractice.com/functions/index). It is checked instantly, then re-checked on changed data.

Lessons that use INDEX:

- [Lesson 14: INDEX & MATCH](https://spreadsheetpractice.com/learn/index-match) (Pro)
- [Lesson 16: Lookups with several criteria](https://spreadsheetpractice.com/learn/multi-criteria-lookups) (Pro)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
