Random cell from list

[tottee]

Hi,

I need to get random cases from a list of cases and is using this so far
=INDEX($A:$A;RANDBETWEEN(2;526))
That what is working fine as long as have not filtered.
What I would like to add is to only pick based on the name in Column 3, like if name =" john, paul, sean" as example, but I cant seem to figure it out

Right now as a work around I filter copy and paste in new sheet, would like to avoid that step

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Here are a couple of options that may be of use.

21 04 01.xlsm
	A	B	C	D	E	F
1	Case		Name		Excel 365	Excel 2010+
2	Case 1		Sean		Case 18	Case 8
3	Case 2		Sean
4	Case 3		Ken
5	Case 4		Paul
6	Case 5		John
7	Case 6		Sean
8	Case 7		Ken
9	Case 8		John
10	Case 9		Sean
11	Case 10		Paul
12	Case 11		Paul
13	Case 12		Ken
14	Case 13		Bob
15	Case 14		John
16	Case 15		Bob
17	Case 16		Sean
18	Case 17		Paul
19	Case 18		Sean
20	Case 19		Paul
21	Case 20		Ken
22	Case 21		Sean
23	Case 22		John
24	Case 23		Ken
Random name

Cell Formulas
Range	Formula
E2	E2	=LET(a,FILTER(A2:A24,ISNUMBER(SEARCH("|"&C2:C24&"|","|John|Paul|Sean|"))),INDEX(a,RANDBETWEEN(1,ROWS(a))))
F2	F2	=INDEX(A:A,AGGREGATE(15,6,ROW(A2:A24)/ISNUMBER(SEARCH("|"&C2:C24&"|","|John|Paul|Sean|")),RANDBETWEEN(1,SUM((C2:C24="John")+(C2:C24="Paul")+(C2:C24="Sean")))))