99

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.

Improve this question
1
  • Can we get a little more context? Are you using just Excel formulas? VBA? Some other method? Commented Jan 27, 2011 at 7:55

3 Answers 3

Reset to default
147

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

This is useful enough that I'd like to see it edited to have the right number of closing parentheses.
Given the popularity of this question/answer, it'd be great to have a little sentence here about what a volatile function is.
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.
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)!
31

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.

Improve this answer

Comments

2

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.