This formula index table does not work for me for picking one name out of list of 20 names. I want Excel to pick names like picking names out of a hat and not repeat the pick of names ever again until all names are picked. Cell A1 is label "Name" and all names fall below lable "Name" from A2:A21 In Cell C1 is label "Random_Picks" with all formulas below in C2:C21
Why does it not work and only shows zeroes in C2:C21 How do I get Excel to pick names from cells A2:A21 once until all names are picked?


Name Random_Picks
Richard =INDEX($A$2:$A$21,SMALL(IF(ISNA(MATCH($A$2:$A$21,$C$2:C25,0)),ROW($A$2:$A$21)-ROW($A$2)+1),INT(RAND()*(ROWS($A$2:$A$21)-(ROW()-ROW($C$2))))+1))
Neil =INDEX($A$2:$A$21,SMALL(IF(ISNA(MATCH($A$2:$A$21,$C$3:C26,0)),ROW($A$2:$A$21)-ROW($A$2)+1),INT(RAND()*(ROWS($A$2:$A$21)-(ROW()-ROW($C$3))))+1))
Larry =INDEX($A$2:$A$21,SMALL(IF(ISNA(MATCH($A$2:$A$21,$C$4:C27,0)),ROW($A$2:$A$21)-ROW($A$2)+1),INT(RAND()*(ROWS($A$2:$A$21)-(ROW()-ROW($C$4))))+1))
Mike =INDEX($A$2:$A$21,SMALL(IF(ISNA(MATCH($A$2:$A$21,$C$5:C28,0)),ROW($A$2:$A$21)-ROW($A$2)+1),INT(RAND()*(ROWS($A$2:$A$21)-(ROW()-ROW($C$5))))+1))
Jeff =INDEX($A$2:$A$21,SMALL(IF(ISNA(MATCH($A$2:$A$21,$C$6:C29,0)),ROW($A$2:$A$21)-ROW($A$2)+1),INT(RAND()*(ROWS($A$2:$A$21)-(ROW()-ROW($C$6))))+1))
Harry =INDEX($A$2:$A$21,SMALL(IF(ISNA(MATCH($A$2:$A$21,$C$7:C30,0)),ROW($A$2:$A$21)-ROW($A$2)+1),INT(RAND()*(ROWS($A$2:$A$21)-(ROW()-ROW($C$7))))+1))
Bill =INDEX($A$2:$A$21,SMALL(IF(ISNA(MATCH($A$2:$A$21,$C$8:C31,0)),ROW($A$2:$A$21)-ROW($A$2)+1),INT(RAND()*(ROWS($A$2:$A$21)-(ROW()-ROW($C$8))))+1))
Pete =INDEX($A$2:$A$21,SMALL(IF(ISNA(MATCH($A$2:$A$21,$C$9:C32,0)),ROW($A$2:$A$21)-ROW($A$2)+1),INT(RAND()*(ROWS($A$2:$A$21)-(ROW()-ROW($C$9))))+1))
Sparky =INDEX($A$2:$A$21,SMALL(IF(ISNA(MATCH($A$2:$A$21,$C$10:C33,0)),ROW($A$2:$A$21)-ROW($A$2)+1),INT(RAND()*(ROWS($A$2:$A$21)-(ROW()-ROW($C$10))))+1))
Joe =INDEX($A$2:$A$21,SMALL(IF(ISNA(MATCH($A$2:$A$21,$C$11:C34,0)),ROW($A$2:$A$21)-ROW($A$2)+1),INT(RAND()*(ROWS($A$2:$A$21)-(ROW()-ROW($C$11))))+1))
Sam =INDEX($A$2:$A$21,SMALL(IF(ISNA(MATCH($A$2:$A$21,$C$12:C35,0)),ROW($A$2:$A$21)-ROW($A$2)+1),INT(RAND()*(ROWS($A$2:$A$21)-(ROW()-ROW($C$12))))+1))
Travis =INDEX($A$2:$A$21,SMALL(IF(ISNA(MATCH($A$2:$A$21,$C$13:C36,0)),ROW($A$2:$A$21)-ROW($A$2)+1),INT(RAND()*(ROWS($A$2:$A$21)-(ROW()-ROW($C$13))))+1))
Kyle =INDEX($A$2:$A$21,SMALL(IF(ISNA(MATCH($A$2:$A$21,$C$14:C37,0)),ROW($A$2:$A$21)-ROW($A$2)+1),INT(RAND()*(ROWS($A$2:$A$21)-(ROW()-ROW($C$14))))+1))
Mario =INDEX($A$2:$A$21,SMALL(IF(ISNA(MATCH($A$2:$A$21,$C$15:C38,0)),ROW($A$2:$A$21)-ROW($A$2)+1),INT(RAND()*(ROWS($A$2:$A$21)-(ROW()-ROW($C$15))))+1))
Brian =INDEX($A$2:$A$21,SMALL(IF(ISNA(MATCH($A$2:$A$21,$C$16:C39,0)),ROW($A$2:$A$21)-ROW($A$2)+1),INT(RAND()*(ROWS($A$2:$A$21)-(ROW()-ROW($C$16))))+1))
Bill =INDEX($A$2:$A$21,SMALL(IF(ISNA(MATCH($A$2:$A$21,$C$17:C40,0)),ROW($A$2:$A$21)-ROW($A$2)+1),INT(RAND()*(ROWS($A$2:$A$21)-(ROW()-ROW($C$17))))+1))
George =INDEX($A$2:$A$21,SMALL(IF(ISNA(MATCH($A$2:$A$21,$C$18:C41,0)),ROW($A$2:$A$21)-ROW($A$2)+1),INT(RAND()*(ROWS($A$2:$A$21)-(ROW()-ROW($C$18))))+1))
Kevin =INDEX($A$2:$A$21,SMALL(IF(ISNA(MATCH($A$2:$A$21,$C$19:C42,0)),ROW($A$2:$A$21)-ROW($A$2)+1),INT(RAND()*(ROWS($A$2:$A$21)-(ROW()-ROW($C$19))))+1))
Phil =INDEX($A$2:$A$21,SMALL(IF(ISNA(MATCH($A$2:$A$21,$C$20:C43,0)),ROW($A$2:$A$21)-ROW($A$2)+1),INT(RAND()*(ROWS($A$2:$A$21)-(ROW()-ROW($C$20))))+1))
Mark =INDEX($A$2:$A$21,SMALL(IF(ISNA(MATCH($A$2:$A$21,$C$21:C44,0)),ROW($A$2:$A$21)-ROW($A$2)+1),INT(RAND()*(ROWS($A$2:$A$21)-(ROW()-ROW($C$21))))+1))