Get content of a cell given the row and column numbers

Question

I want to get the content of a cell given its row and column number. The row and column number are stored in cells (here B1,B2). I know the following solutions work, but they feel a bit hacky.

Sol 1

=CELL("contents",INDIRECT(ADDRESS(B1,B2)))

Sol 2

=CELL("contents",OFFSET($A$1, B1-1,B2-1))

Is there no less verbose method? (like =CellValue(row,col) or whatever)?

Edit / Clarification: I just want to use the excel worksheet formulas. No VBA. In short, I pretty much look for the equivalent of the VBA Cells() method as an excel Formula.

Can we get a little more context? Are you using just Excel formulas? VBA? Some other method? — Saladin Akara, Jan 27 '11 at 07:55

Charles Williams · Accepted Answer · 2015-07-08T19:22:01.257

142

You don't need the CELL() part of your formulas:

=INDIRECT(ADDRESS(B1,B2))

or

=OFFSET($A$1, B1-1,B2-1)

will both work. Note that both INDIRECT and OFFSET are volatile functions. Volatile functions can slow down calculation because they are calculated at every single recalculation.

edited Jul 08 '15 at 19:22

answered Jan 27 '11 at 08:31

Charles Williams

23,121
5
38
38

This is useful enough that I'd like to see it edited to have the right number of closing parentheses. – Wyck Mar 14 '14 at 18:15
7

Given the popularity of this question/answer, it'd be great to have a little sentence here about what a [volatile function](https://msdn.microsoft.com/en-us/library/office/bb687891.aspx#sectionSection2) is. – LondonRob Jul 08 '15 at 17:24
Note that with the second form, depending on $a$1, it is required that you do not move $a$1 in any way in later edits of the sheet. – Erk Dec 10 '16 at 20:54
`INDIRECT` +`ADDRESS` is the way to go. Excel in my language translates `OFFSET` into 2 words separated by a period. That is the worst (together with umlauts in function names)! – Bitterblue Sep 19 '17 at 08:45

score 29 · Answer 2 · answered Apr 02 '13 at 21:43

29

Try =index(ARRAY, ROW, COLUMN)

where: Array: select the whole sheet Row, Column: Your row and column references

That should be easier to understand to those looking at the formula.

answered Apr 02 '13 at 21:43

Albert Shenko

299
3
3

score 2 · Answer 3 · edited Feb 26 '18 at 15:31

It took me a while, but here's how I made it dynamic. It doesn't depend on a sorted table.

First I started with a column of state names (Column A) and a column of aircraft in each state (Column B). (Row 1 is a header row).

Finding the cell that contains the number of aircraft was:

=MATCH(MAX($B$2:$B$54),$B$2:$B$54,0)+MIN(ROW($B$2:$B$54))-1

I put that into a cell and then gave that cell a name, "StateRow" Then using the tips from above, I wound up with this:

=INDIRECT(ADDRESS(StateRow,1))

This returns the name of the state from the dynamic value in row "StateRow", column 1

Now, as the values in the count column change over time as more data is entered, I always know which state has the most aircraft.

Get content of a cell given the row and column numbers

3 Answers3

Linked