I want to reference a cell in an Excel formula. However, I want the row number to be based on a cell value in another cell. For example, I don't just want to reference cell R2, I want to say, reference the cell that is in column R, and has the row value I have listed in column V2.
Thank you for your help!
Use INDEX:
=INDEX(R:R,V2)
This will return the value of the cell in Column R and the row number that is entered in V2.
the indirect formula should do it:
=indirect("R"&V2)
just to give out options. OFFSET which is a volatile function will pick and return cells or ranges based on a given cell. Volatile function will recalculate anytime anything on sheet changes. Non-volatile functions only recalculate when when cells that affect them change. Therefore a lot of volatile functions on a sheet can cause an unnecessary amount of excess calculations. INDEX is the better solution.
OFFSET take the following form
OFFSET(BASE CELL, HOW MANY ROWS TO MOVE DOWN, HOW MANY COLUMNS TO MOVE OVER, HOW MANY ROWS TO RETURN, HOW MANY COLUMNS TO RETURN)
In your case I would be tempted to make the formula look as follows:
=OFFSET($R$1,V2,0,1,1)
