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.

On 3 Aug, 14:30, onewebclick
wrote:
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.

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

"onewebclick" wrote:

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.

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


"onewebclick" wrote:

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.