0

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!

1
  • 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
4

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.

1

the indirect formula should do it:

=indirect("R"&V2)
1
  • 2
    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
0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.