'Random cell with index & match with extra criteria in another column

I got three columns: Level, Calculation and Answer. To choose a random calculation, I use the formula: =INDEX(B2:B16, RANDBETWEEN(1, COUNTA(B2:B16)))

To get the related answer, I use the formula: =INDEX($C$2:$C$16,MATCH(F2,$B$2:$B$16,0))

Now I want to choose a random calculation based on the level in the first column. So instead of choosing a random calculation from the 15 calculations in column B, I want it to choose a random calculation from column B where column A for example is '2'.

Is this possible and if yes, how?

Example of my spreadsheet

excelgoogle-sheetsrandomexcel-formulagoogle-sheets-formula


Solution 1:[1]

use:

=QUERY({FILTER(A2:C, A2:A<>""), RANDARRAY(COUNTA(A2:A))}, 
 "select Col2,Col3 where Col1 = 2 order by Col4 limit 1")

enter image description here

Solution 2:[2]

Assuming you put your chosen level in G1:

=LET(?,FILTER(B2:B16,A2:A16=G1),INDEX(?,RANDBETWEEN(1,ROWS(?))))

Solution 3:[3]

You could make a unique list of the levels and put that in a range to provide user entry for the level selector, then use the level to filter the table. It seems to work but you might want to test it. image of possible solution

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 player0
Solution 2 Jos Woolley
Solution 3 Troystav

Related Questions