how do i do this in excel ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 2 column a,b with the following row in the below format,

A B
-----------
1 10
2 36
3 12
4 8
5 2
6 30

Then in the C colum the rows are generated using this function:
=ROUND(RAND()*(7-1)+1,0), it changes radomly because of the rand function
between 1-6.

C
---
3
2
1
4
6
5
2
2

I need a D colum in such a way for a number 'N' in C row I want to pick
the 'Nth' row in A colum and display the B's Nth row. so for (eg) in this
case it would be,

D
---
12
36
10
8
30
2
36
36

please help on what function should i use to generate the D column.
 
I have 2 column a,b with the following row in the below format,

A B
-----------
1 10
2 36
3 12
4 8
5 2
6 30

Then in the C colum the rows are generated using this function:
=ROUND(RAND()*(7-1)+1,0), it changes radomly because of the rand function
between 1-6.

C
---
3
2
1
4
6
5
2
2

I need a D colum in such a way for a number 'N' in C row I want to pick
the 'Nth' row in A colum and display the B's Nth row. so for (eg) in this
case it would be,

D
---
12
36
10
8
30
2
36
36

please help on what function should i use to generate the D column.
Click to expand...

vlookup would do it from what I can see of your explantion
 
Here's a way of doing it:
A B C D
1 10 rand# =OFFSET($A$1,C1-1,1)

Tried it, and it seems to work fine.
HTH
 
In C1: =ROUNDUP(RAND()*6,0)
or
=RANDBETWEEN(1,6) ----> Required Analysis ToolPak Add-Ins
copy down

In D1: =VLOOKUP(C1,$A$1:$B$6,2,0)
copy down
 
Back
Top