Lookup
Picking a cell by row number
Definition
Picking a cell by row number means giving INDEX a column and the position of the row inside that column.
Syntax
=INDEX(array, row_num, [column_num])
Arguments
| Argument | Meaning |
|---|---|
array |
The column to read from, such as B2:B4. |
row_num |
The position inside the range, counting its first row as 1. |
column_num |
Optional. Not needed when the range is a single column. |
Worked example
A pack list stores unit counts in B2:B4. The third count is the one to return.
| A: Pack | B: Units |
|---|---|
| A | 5 |
| B | 15 |
| C | 25 |
Formula: =INDEX(B2:B4,3)
Result: 25. The third cell inside B2:B4 is B4, which holds 25.
Practice
Cells A2:A4 hold 10, 20, and 30. In B2, write a formula that returns row 2 of A2:A4.
- In B2, write an INDEX formula that returns row 2 of A2:A4.
- B2 should show row 2 of A2:A4.
Step 1 of 2
Enter =INDEX(A2:A4,2) in B2.
Row 2 of A2:A4 is 20.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
B2 uses INDEX.
B2 now shows 20.
The next lesson is Picking a cell from one row.
Related functions
INDEX is the function that returns the value at that row position.
Common mistake
Using the sheet row number, as in =INDEX(B2:B4,4): B4 is sheet row 4, but that position does not exist inside B2:B4, so the result is #REF! instead of 25.