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.

Answer 1

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.

Answer 2

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.

Answer 3

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.