Lookup and reference
INDEX function playground
Returns the value at a given row and column position in a range.
The playground runs in your browser. Nothing you type is sent anywhere.
How INDEX works
INDEX counts down row_num rows and across column_num columns inside the range and returns that cell. The numbers are positions inside the range, not sheet row numbers.
On its own INDEX needs you to know the position. Pair it with MATCH, which finds the position, to make a flexible lookup that works in any direction.
Syntax
=INDEX(array, row_num, [column_num])| Argument | What it means |
|---|---|
arrayrequired | The range to pick from. |
row_numrequired | Which row of the range, counting from 1. |
column_numoptional | Which column of the range. Not needed for a single column. |
Examples
| Formula | What it does | Result |
|---|---|---|
=INDEX(B2:B9,4) | The 4th name in the list. | Jonas Berg |
=INDEX(A2:E9,3,4) | Row 3, column 4 of the table: Lily Chen's salary. | $81,000.00 |
=INDEX(B2:B9,MATCH(I1,A2:A9,0)) | INDEX and MATCH: the name for the ID in I1. | Jonas Berg |
=INDEX(D2:D9,MATCH("IT",C2:C9,0)) | Salary of the first IT employee. | $81,000.00 |
Common mistakes
Sheet rows vs positionsINDEX(B2:B9,4) returns B5, the 4th cell of the range, not row 4 of the sheet.
Position too largeA row or column past the range edge gives #REF!.
Related functions
Learn more
Download the example workbook (.xlsx)
The workbook has the same sample data and formulas, plus a practice sheet.