Matching two columns of data

[JFLaj]

Hi All,

I have two columns of numbers that need to be kept as pairs for example

122 50
122 90
345 657
555 67

I need to randomly choose a pair x amount of times. I am using thi
formula in the first output colum
=INDEX($I$2:$I$5,RANDBETWEEN(1,COUNTA($I:$I)),1), which is fine t
choose one of the numbers of column 1, however I am struggling to matc
the randomly chosen first number with its corresponding second number
This is as good as I have go
=INDEX($J$2:$J$5,(MATCH(L2,$I$2:$I$5,0)),). But the problem with is tha
is chooses the first value it finds that corresponds to the correc
number in column 1. So 122 is always paired with 505 never 909. I need
way to match the randomly chosen cell location rather than the valu
itself.

Hope this makes sense. Thanks for any help

 

[Claus Busch]

Hi,

Am Wed, 21 Nov 2012 13:19:41 +0000 schrieb JFLaj:

122 505
122 909
345 6576
555 676

I need to randomly choose a pair x amount of times. I am using this
formula in the first output column

MATCH or VLOOKUP only find the first value. Randomly choose the first
value in the column with unique values.


Regards
Claus Busch

 

[lhkittle]

Hi All,



I have two columns of numbers that need to be kept as pairs for example;



122 505

122 909

345 6576

555 676



I need to randomly choose a pair x amount of times. I am using this

formula in the first output column

=INDEX($I$2:$I$5,RANDBETWEEN(1,COUNTA($I:$I)),1), which is fine to

choose one of the numbers of column 1, however I am struggling to match

the randomly chosen first number with its corresponding second number.

This is as good as I have got

=INDEX($J$2:$J$5,(MATCH(L2,$I$2:$I$5,0)),). But the problem with is that

is chooses the first value it finds that corresponds to the correct

number in column 1. So 122 is always paired with 505 never 909. I need a

way to match the randomly chosen cell location rather than the value

itself.



Hope this makes sense. Thanks for any help.

Hi JFLaj,

Maybe this will work for you...

A1:A4 enter 1,2,3,4
B1:C4 is your example table
D1 enter =RANDBETWEEN(1,4)
Select BOTH cells E1 & F1 and enter =VLOOKUP(D1,A1:C4,{2,3},0)
Commit with ctrl + shift + enter.
Hit F9 to recalc results.

HTH
Regards,
Howard