Random Questions from specified Category

[Tunes81]

Hello,
I am trying to create a quiz generator that will pull 10 random non duplicate questions from a question bank. I was able to get a random question from the bank by using this forumula: =INDEX(A2:A500,ROUND(MRAND()*COUNTA(A2:A500),1))
How can I get this formula to only give me a question that is in the same row as a specified category? Any help would be greatly appreciated!

 

[Tunes81]

Sorry this is the formula:
=INDEX(A2:A500,MROUND(RAND()*COUNTA(A2:A500),1))

 

[JGordon11]

If you can use VBA a custom function can do that

Function RandomQuestions(QuestionBank As Range, Optional NumberOfQuestions As Long = 10, Optional Category As String = "")
    Dim QB, QB1, i As Long, ub As Long, j As Long, idx As Long, k As Long
    QB = QuestionBank
    ub = UBound(QB)
    For i = 1 To ub
        If QB(i, 1) = Category Or Category = "" Then
            j = j + 1
            QB(j, 1) = QB(i, 2)
        End If
    Next
    If j = 0 Then
        RandomQuestions = "Category not found"
        Exit Function
    End If
    ReDim QB1(1 To j)
    ReDim Output(1 To NumberOfQuestions, 1 To 1)
    For i = 1 To j
        QB1(i) = QB(i, 1)
    Next
    For i = j To j - NumberOfQuestions + 1 Step -1
        k = k + 1
        If i > 0 Then
            idx = Int(Rnd() * i) + 1
            Output(k, 1) = QB1(idx)
            QB1(idx) = QB1(i)
        Else
            Output(k, 1) = ""
        End If
    Next
    RandomQuestions = Output
End Function

RandomQuestions.xlsm
	A	B	C	D	E	F	G	H
1	Category	Question
2	A	Q1A		All	Q14B		D	Category not found
3	B	Q2B			Q26B			Category not found
4	C	Q3C			Q17B			Category not found
5	A	Q4A			Q20B			Category not found
6	B	Q5B			Q11B			Category not found
7	C	Q6C			Q8B
8	A	Q7A			Q19A
9	B	Q8B			Q12C
10	C	Q9C			Q7A
11	A	Q10A			Q10A
12	B	Q11B
13	C	Q12C		A	Q10A
14	A	Q13A			Q19A
15	B	Q14B			Q4A
16	C	Q15C			Q7A
17	A	Q16A			Q22A
18	B	Q17B
19	C	Q18C		C	Q21C
20	A	Q19A			Q24C
21	B	Q20B			Q12C
22	C	Q21C			Q6C
23	A	Q22A			Q15C
24	B	Q23B			Q3C
25	C	Q24C			Q27C
26	A	Q25A			Q18C
27	B	Q26B			Q9C
28	C	Q27C
Sheet5

Cell Formulas
Range	Formula
H2:H6	H2	=RandomQuestions($A$2:$B$28,5,"D")
E2:E11	E2	=RandomQuestions($A$2:$B$28)
E13:E17	E13	=RandomQuestions($A$2:$B$28,5,D13)
B2:B28	B2	="Q"&ROW()-1 & A2
E19:E28	E19	=RandomQuestions($A$2:$B$28,11,"C")
Press CTRL+SHIFT+ENTER to enter array formulas.