[excel] Excel select a value from a cell having row number calculated

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 related to excel excel-formula

The answer is


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.