INDEX
Return the value at a given row (and optionally column) position within a range.
Syntax
INDEX(array, row_num, [column_num])| Argument | Required | Description |
|---|---|---|
array | Required | The range to pull a value from. |
row_num | Required | Which row of the range, counted from 1. |
column_num | Optional | Which column of the range. Omit it when the range is a single column. |
Examples
Every example below was executed against VisiGrid engine 0.35.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=INDEX(C2:C4,2) | Bo | Second entry of a single-column range. |
=INDEX(A2:D4,2,3) | Bo | Row 2, column 3 of a rectangular range. |
=INDEX(C2:C4,MATCH(102,A2:A4,0)) | Bo | The classic pairing — MATCH finds the position, INDEX fetches the value. |
On its own it is rarely useful
INDEX answers “what is in position N”, and you usually don’t know N — you know
a value you want to find. That is why it appears almost exclusively paired with
MATCH, which supplies the position:
=INDEX(C2:C4, MATCH(102, A2:A4, 0))Read it inside-out: MATCH finds which row contains 102, INDEX returns the
value at that row of column C.
Why the pairing survived
INDEX/MATCH was for twenty years the only way to look leftward, because
VLOOKUP can only return columns to the right
of the one it searches. The two functions take independent ranges, so neither
cares which side of the other your data sits on.
XLOOKUP now does the same job in one function and reads more clearly. INDEX
and MATCH remain worth knowing because they appear in every inherited
spreadsheet, and because INDEX is genuinely useful on its own when the
position really is what you have.
Excel compatibility
Matches Excel for both the single-column and row-and-column forms.