The INDEX function in Excel
INDEX answers "what's in row 3, column 2 of this range?". On its own that's simple; together with MATCH, which finds the position, it becomes a lookup that works in any direction and in every Excel version.
Syntax
=INDEX(array, row_num, [column_num])
| Argument | What it means |
|---|---|
| array | The range to take a value from. |
| row_num | The row inside that range (1 = its first row). |
| column_numoptional | The column inside that range. Not needed when the range is a single column. |
Examples
1. Row and column of a table
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Department | Office | Row 3, column 2 | |
| 2 | Rina | Sales | Tel Aviv | IT | |
| 3 | Omar | Finance | Haifa | ||
| 4 | Lena | IT | Berlin | ||
| 5 | Tom | Sales | London |
Inside A2:C5, row 3 is Lena's row and column 2 is Department: IT.
2. One column: only the row number
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Department | Office | 4th office | |
| 2 | Rina | Sales | Tel Aviv | London | |
| 3 | Omar | Finance | Haifa | ||
| 4 | Lena | IT | Berlin | ||
| 5 | Tom | Sales | London |
With a single column, the second argument is the position in the list.
3. INDEX/MATCH: a lookup by name
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Department | Office | Omar's office | |
| 2 | Rina | Sales | Tel Aviv | Haifa | |
| 3 | Omar | Finance | Haifa | ||
| 4 | Lena | IT | Berlin | ||
| 5 | Tom | Sales | London |
MATCH finds Omar at position 2 of the names; INDEX returns position 2 of the offices.
Common mistakes
#REF! error. The row or column number is larger than the range, e.g. row 5 of a 4-row range.
Fix: Count positions inside the range, not sheet row numbers.
Using sheet row numbers. In A2:C5, sheet row 2 is position 1.
Fix: Let MATCH work out the position for you.
Frequently asked questions
Why use INDEX/MATCH instead of VLOOKUP?
It can return values from columns to the left, it doesn't break when you insert columns, and it works in every Excel version.
Is INDEX/MATCH still needed with XLOOKUP?
In Excel 365, XLOOKUP is simpler. INDEX/MATCH remains common in existing files and is needed for older versions.
Related functions
- MATCHMATCH returns the position of a value in a list, for example that "Lena" is the 3rd name.
- XLOOKUPXLOOKUP searches one column or row and returns the matching value from another, in any direction, with a built-in 'not found' message.
- VLOOKUPVLOOKUP finds a value in the first column of a table and returns a value from the same row in another column.