I am trying to create a formula that will generate a random number of answers from a table and return it as one combined answer.
I thought something like this might work:
=REPT(VLOOKUP(RANDBETWEEN(1,5),A1:B5,FALSE()),RANDBETWEEN(1,3))
The Idea is I have a table with 2 columns and 5 rows. I want to randomly generate a number for column A for vlookup to pull for column B. Than I want to repeat this equation a random number of times between 1 and 3. So that when the formula is complete I will have 1 to 3 randomly generated answers from the 5 rows in column B.
Example:
Column A reads 1 in cell A1, 2 in cell A2, ect.
Column B reads A in cell1B, B in cell 2B, ect.
So in cell 1C, where I would input the formula, I get an answer like: D, A, B
The formula I posted at the top was my best guess. It currently returns an error message of #VALUE!
I'm not sure if I'm misusing the formula or not, or if there is a better one to use.
Column B will end of having phrases in each cell, if that makes a difference.
I thought something like this might work:
=REPT(VLOOKUP(RANDBETWEEN(1,5),A1:B5,FALSE()),RANDBETWEEN(1,3))
The Idea is I have a table with 2 columns and 5 rows. I want to randomly generate a number for column A for vlookup to pull for column B. Than I want to repeat this equation a random number of times between 1 and 3. So that when the formula is complete I will have 1 to 3 randomly generated answers from the 5 rows in column B.
Example:
Column A reads 1 in cell A1, 2 in cell A2, ect.
Column B reads A in cell1B, B in cell 2B, ect.
So in cell 1C, where I would input the formula, I get an answer like: D, A, B
The formula I posted at the top was my best guess. It currently returns an error message of #VALUE!
I'm not sure if I'm misusing the formula or not, or if there is a better one to use.
Column B will end of having phrases in each cell, if that makes a difference.