Syntax
INDEX(array, row_num, [col_num])
| Parameter | Description |
|---|---|
| array | Parameter of the INDEX function. |
| row_num | Parameter of the INDEX function. |
| [col_num] | (Optional.) Parameter of the INDEX function. |
Examples
| A | B | C | |
|---|---|---|---|
| 1 | Product | SKU | Price |
| 2 | Widget | W-100 | 24.99 |
| 3 | Gadget | G-200 | 49.99 |
| 4 | Gizmo | Z-300 | 14.99 |
| 5 | Doohickey | D-400 | 34.99 |
Get value at a specific position
Formula
=INDEX(B2:B10, 3)
Returns: the value in the 3rd row of the range B2:B10 (which is cell B4)
Two-dimensional lookup
Formula
=INDEX(B2:D10, 3, 2)
Returns: the value at row 3, column 2 of the range — effectively cell C4
INDEX-MATCH lookup
Formula
=INDEX(C2:C100, MATCH("Widget", A2:A100, 0))
Returns: the price from column C for the row where column A contains "Widget"
Common Errors
#REF!
The row or column number exceeds the size of the range. For example, requesting row 15 from a 10-row range.
#VALUE!
The row or column argument is not a valid number. It must be a positive integer.
Tips
Why INDEX-MATCH over VLOOKUP
INDEX-MATCH can look up to the left, does not break when columns are inserted, and is faster on large datasets.
Return entire row or column
Use 0 as the row or column number to return the entire row or column as an array.
Dynamic ranges
INDEX can define dynamic range endpoints: =SUM(A1:INDEX(A:A, E1)) sums from A1 to whatever row number is in E1.
Try INDEX in Viztab
Import your data and use INDEX with 370+ other formulas. No signup required.
Open Viztab