CODEWITHSUNDEEP
excel
YozSap
YozSap

Reputation: 97

Excel How do you get the value of a cell via another cell?

This may be a really dumb question.

In Col A, I have a column of values. In Col J, I have the row number of the values in Col A that I want. How do I use this to get the values?

For example

Col A

row 1: bob

row 2: joe

row 3: apple

row 4: tom

row 5: tim

Col B

1

4

5

In another column, I basically want:

bob

tom

tim

I have about 300 of these values.

Upvotes: 0

Views: 1848

Answers (4)

Bill the Lizard
Bill the Lizard

Reputation: 405745

You can use the INDEX function for this. Given columns A and B as you defined, you can put =INDEX(A1:A5,B1) in another column. That says to index (look up in) the array A1:A5 with the pointer in cell B1. Since B1 has the value 1, this is equivalent to =INDEX(A1:A5,1), and returns "bob".

If you want to copy that to multiple cells, make sure you keep the array (A1:A5) part constant, and just change the cell reference (B1). Your formulas would then be =INDEX(A$1:A$5,B1), =INDEX(A$1:A$5,B2), etc.

Upvotes: 1

Jomathr
Jomathr

Reputation: 174

You can use the indirect function to accomplish that:

for example in column c you cou use the formula:

=INDIRECT("A" & B2, TRUE)

This would take the row number in your cell B2 and get the value in the A column.

Hope this helps!

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

Use the Index () worksheet function.

Upvotes: 1

James Hawkins
James Hawkins

Reputation: 218

If these were in 3 columns with headings, such that under some heading, say "names" in A2 would be "bob" with other names below; and under another heading, say "row" in B2 would be 1, with other numbers below, then, under some 3rd heading, say "result":

In C2, copied down
=OFFSET(A1,B2,0)

Upvotes: 0

Related Questions