# How to variably choose a cell reference?

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.

• Welcome to Super User! Could you provide some sample data and the output you would like to see? – Worthwelle Mar 22 '19 at 17:20

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)
``````
• One note on INDIRECT, it is volatile and will recalc every time Excel recalcs. Having one or two will not impact the calc times but many will. Otherwise this is a good option. – Scott Craner Mar 22 '19 at 17:01

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)
``````