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

User · Answer

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

User · Answer

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

User · Answer

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

[excel] Get content of a cell given the row and column numbers

Examples related to excel

Examples related to excel-formula