How to pick any cell from any table — and why it's the secret engine behind BYROW
Imagine you have a range of cells — a box you've drawn around some data. INDEX's job is dead simple: you tell it which row and column inside that box, and it gives you back the value at that spot.
That's it. INDEX is a coordinate lookup. Row, column, done.
The Aha Moment: INDEX doesn't care where your box is on the spreadsheet. Column G is Column G to you, but to INDEX it's just "the 1st column of whatever box you gave me." Think of it like seat numbers in a cinema — the usher doesn't know the cinema's street address, only the row and seat number inside the hall.
The syntax is: =INDEX(range, row_number, column_number)
Using our 4-row, 5-column sales table:
| A — Rep | B — Region | C — Units | D — Price | E — Total | |
|---|---|---|---|---|---|
| Row 1 | Alice | North | 100 | 10 | 1000 |
| Row 2 | Bob | South | 200 | 15 | 3000 |
| Row 3 | Charlie | East | 150 | 20 | 3000 |
| Row 4 | Dana | West | 300 | 5 | 1500 |
So =INDEX(A1:E4, 1, 2) returns North — because inside the box A1:E4, row 1 and column 2 is the Region for Alice.
Drag the sliders to pick a row and column. Watch the grid highlight the cell INDEX would return.
Every example below uses the same table (A1:E4). Notice how the logic column explains it all — once you trust the coordinates, reading INDEX formulas becomes instant.
| # | What We Want | Formula | Result | Why |
|---|
🧠 Quick Check — Apply what you just read
Your manager adds a 6th column "Commission" to the table (now A1:F4). She asks you to write a formula that fetches Charlie's Commission.
The Golden Rule: INDEX coordinates are relative to the selection, not the sheet. If your range starts at Column G, INDEX(range, 1, 1) returns the value in Column G — not Column A.
This trips up almost every new INDEX user. Here's a concrete example:
| Scenario | Range | Formula | Returns | Because… |
|---|---|---|---|---|
| Range starts at A | A1:E4 | =INDEX(A1:E4,1,1) | Alice | Col 1 of box = column A |
| Range starts at C | C1:E4 | =INDEX(C1:E4,1,1) | 100 | Col 1 of box = column C (Units) |
| Single-column range | C1:C4 | =INDEX(C1:C4,2,1) | 200 | Only 1 column, so col must be 1 |
🧠 Debugging Scenario
A colleague writes this formula expecting to get Bob's Region:
Instead of "South" they get 200. What went wrong?
BYROW is a powerful function — but it shrinks your map. When BYROW hands a row to your LAMBDA, you're no longer working with the full table. You're working with a single-row slice.
Consider: =BYROW(B1:E4, LAMBDA(row, INDEX(row, 1, 3)))
Here BYROW hands INDEX a slice that is only 4 columns wide (B through E). So column 3 inside that slice is column D — which is Price.
Mental model: BYROW tears out one strip of paper at a time and says "here, work with this." INDEX then points to a spot on that small strip — not on the original full sheet.
Click through the animation below to see BYROW hand each row to INDEX one by one:
Formula: =BYROW(B1:E4, LAMBDA(row, INDEX(row, 1, 3))) — picks column 3 (Price) from each row
🧠 Apply It: BYROW + INDEX
You want to use BYROW to extract the Total (column E) from each row of the full table (A1:E4). Which formula is correct?