Excel INDEX: Return a Cell by Position
By Szabó Gergő · Updated
INDEX returns the value at a position inside a range. Combined with MATCH it can look left, right, or two-way.
Syntax and arguments
=INDEX(array, row_num, [column_num])- array
- The range that contains the result.
- row_num
- Which row inside that range, starting at 1.
- column_num
- Optional column inside a 2D array.
INDEX examples
01
Nth item
List in A2:A20, n in C1.
=INDEX(A2:A20,C1)C1=3 returns the third item in the list.
02
Two-way
Table A2:D20, row 4, column 3.
=INDEX(A2:D20,4,3)Returns the cell at that offset, not worksheet row 4 unless the range starts at row 1.
03
Whole row spill
Microsoft 365.
=INDEX(A2:D20,3,0)Column 0 returns the entire third row of the array.
Common mistakes
Using a worksheet row number
row_num is relative to the array. INDEX(A2:A20,2) is A3.
Omitting MATCH
INDEX alone does not search. Use MATCH or XMATCH for a lookup.
#REF! from a too-large row_num
The position must fit inside the array.
INDEX FAQ
INDEX vs XLOOKUP?
XLOOKUP is easier for one lookup. INDEX is the building block for two-way and older files.
Can INDEX return a range?
Yes. Other functions can use INDEX to build a start or end cell.
Is INDEX volatile?
No. OFFSET is volatile. Prefer INDEX for performance.