Excel select a value from a cell having row number calculated

13

In column A i have numbers calculated like so:

[A]
[1]
[2]
[3]
[4]
[1]
[2]
[3]
...

In column K I have values:

[row] [K]
[ 1 ] [7]
[ 2 ] [3]
[ 3 ] [6]
[ 4 ] [9]

How could I pull values from column K given that row number is the number stored in column A?
I have tried using Address() and Cell() like so:

=Cell("contents",ADDRESS(A5,11))

ADDRESS supposed to return a reference to given cell provided row and column numbers (that can be taken from other cells), CELL supposed to take reference and return detail about the cell referenced (here "content" and thus value). If I use it like so:

=Cell("contents", K4)

it gives me '9', when I use ADDRESS:

=ADDRESS(A4,11)

it gives me $K$4. But when I use them together I get an error.

This question is tagged with excel excel-formula

~ Asked on 2013-11-29 10:20:45

The Best Answer is


27

You could use the INDIRECT function. This takes a string and converts it into a range

More info here

=INDIRECT("K"&A2)

But it's preferable to use INDEX as it is less volatile.

=INDEX(K:K,A2)

This returns a value or the reference to a value from within a table or range

More info here

Put either function into cell B2 and fill down.

~ Answered on 2013-11-29 10:31:27


Most Viewed Questions: