Excel track · Exercise 45 of 86 Paid
INDEX: a value at a row and column
Read any single cell of a grid from a row number and a column number.
Intermediate
Three products across three quarters
Read-only data · answer in B7
In B7, read the units at the row in B5 and the column in B6, counting from the first product and from Q1.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Q1 | Q2 | Q3 |
| 2 | Widget | 100 | 120 | 130 |
| 3 | Gadget | 90 | 110 | 125 |
| 4 | Gizmo | 140 | 160 | 150 |
| 5 | Row to read | 3 | ||
| 6 | Column to read | 2 | ||
| 7 | Units |
Reference answer
What it does
INDEX reads one cell out of a rectangular block. The block is B2:D4, so Widget's Q1 sits at B2, which is position 1, 1. Counting three rows down and two columns across lands on C4, which holds 160. The result goes in B7.
The count restarts at the top-left corner of whatever range you hand it. Row 1 of the block is sheet row 2, and column 1 is column B, so the 3 and 2 in B5 and B6 count from B2.
Feed those numbers from cells and the same formula follows the question. Change B6 to 3 and B7 reads 150, Gizmo's Q3. Change B5 to 2 and it reads 110, Gadget's Q2. Type 3 and 2 into the formula and B7 keeps answering for a cell nobody asked about.
Syntax
=INDEX(array, row_num, [column_num])Worked example
On Three products across three quarters, =INDEX(B2:D4,B5,B6) returns 160.
=INDEX(B2:D4,B5,B6)Common errors
| Error | What it means and how to fix it |
|---|---|
#REF! | The row or column number points outside B2:D4, as when 9 is asked for from a three-column block. |
#VALUE! | The row or column argument arrived as text, which INDEX cannot count with. |
Wrong cell | B5 and B6 went in as column then row, so the formula reads Widget's Q3 at 130 and no error appears. |
At work
Reading one quarter of one product out of a sales grid, with the quarter picked from a drop-down.