Formula to return random number from a list and associated name

My objective is to take two different random counts of numbers written in column b, ranging from B9:B66. However I want to highlight the specific fields listed in the example below, and pull random numbers from those selected fields only. Then I want to pull another set of random numbers in the same column B9:B66, however using a different specific set of fields within column B; as shown in the second random formula string. Use the Press 9 feature to randomly select the two different numbers. What this attempt is trying accommplish is selecting one boy, one girl who have assigned numbers adjacent to their names(name field populated in column c).
What am I doing in correctly?

HTML Code: 

I think you might just have your index range wrong...

it should be C9:C66 if you want to pull names from Column C corresponding to unique numbers in column B.

e.g.

Please Login or Register  to view this content.

I don't think you'll get this to work with that approach using INDEX. You could probably use a similar approach with CHOOSE, i.e

=CHOOSE(INT(RAND()*COUNTA(B9,B13,B15,B16,B17,B20,B21,B23,B25,B31,B33,B34,B35,B37,B38,B39,B41,B42,B44,B45,B47,B50,B51,B52))+1,B9,B13,B15,B16,B17,B20,B21,B23,B25,B31,B33,B34,B35,B37,B38,B39,B41,B42,B44,B45,B47,B50,B51,B52)

but CHOOSE limits you to 29 arguments.

I'd suggest using a helper column, e.g. in D9:D66 put either "B" or "G" to indicate boy or girl on each row (you can hide this column if you want) then you can use this formula to generate a random "B" number from column B

=INDEX(B9:B66,SMALL(IF(D9:D66="B",ROW(D9:D66)-ROW(D9)+1),INT(RAND()*COUNTIF(D9:D66,"B")+1)))

This is an "array formula" that needs to be confirmed with CTRL+SHIFT+ENTER. To do this select the cell with formula, press F2 key then hold down CTRL and SHIFT keys and press ENTER so that curly braces like { and } appear around the formula in the formula bar.

Note: I've used RAND() rather than RANDBETWEEN to avoid ATP functions.....but you could also use RANDBETWEEN.....

Note2: I don't know if you expect any cells to be blank in B9:B66. If there are then CHOOSE approach won't work correctly unless those blanks are progressively from the bottom of the range

You are right, ddl... I should've tested it more extensively.....

Originally Posted by daddylonglegs

I don't think you'll get this to work with that approach using INDEX. You could probably use a similar approach with CHOOSE, i.e

=CHOOSE(INT(RAND()*COUNTA(B9,B13,B15,B16,B17,B20,B21,B23,B25,B31,B33,B34,B35,B37,B38,B39,B41,B42,B44,B45,B47,B50,B51,B52))+1,B9,B13,B15,B16,B17,B20,B21,B23,B25,B31,B33,B34,B35,B37,B38,B39,B41,B42,B44,B45,B47,B50,B51,B52)

but CHOOSE limits you to 29 arguments.

I'd suggest using a helper column, e.g. in D9:D66 put either "B" or "G" to indicate boy or girl on each row (you can hide this column if you want) then you can use this formula to generate a random "B" number from column B

=INDEX(B9:B66,SMALL(IF(D9:D66="B",ROW(D9:D66)-ROW(D9)+1),INT(RAND()*COUNTIF(D9:D66,"B")+1)))

This is an "array formula" that needs to be confirmed with CTRL+SHIFT+ENTER. To do this select the cell with formula, press F2 key then hold down CTRL and SHIFT keys and press ENTER so that curly braces like { and } appear around the formula in the formula bar.

Note: I've used RAND() rather than RANDBETWEEN to avoid ATP functions.....but you could also use RANDBETWEEN.....

Note2: I don't know if you expect any cells to be blank in B9:B66. If there are then CHOOSE approach won't work correctly unless those blanks are progressively from the bottom of the range

adding a column D, using the "B", "G" suggestion works-thank you, is there a way to pull the corresponding name down into column "C70" and "C72", so the name of the number holder randomly selected appears adjacent number?

ie.

Boys

25 Joe Smith

Girls

59 Mary Smith

gmaz2,

Please read the forum rules about thread titles before posting again.

You can change the Index range in daddylonglegs' formula as I had initially suggested...

Please Login or Register  to view this content.

or if you are showing the number and want the name beside it, you could use Vlookup...

Please Login or Register  to view this content.

where I assume X1 contains the randomly pulled number.. change as necessary.

Thanks, the VLOOKUP Solution works. Thanks for your assistance and patience through all of this.
Gmaz2