Can I combine randbetween, rept, and vlookup to get multiple answers?

Marla89

New Member
Joined
Apr 15, 2024
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Book1
ABC
11AC, A, B
22B
33C
44D
55E
6
Sheet2
Cell Formulas
RangeFormula
A1:A5A1=SEQUENCE(5)
C1C1=TEXTJOIN(", ",1,TAKE(SORTBY(B1:B5,RANDARRAY(5)),RANDBETWEEN(1,3)))
Dynamic array formulas.
 
Upvote 0
@JGordon11
I see how that can work, but I do need to use column A as the reference rather than randomizing from column B.
 
Upvote 0
Why? That would require a longer and more complicated formula to get the same result.
Because some of the tables I would like to apply the formula to require me to randomly generate a number from column A and than add 20 (as an example, this number changes sometimes or isn't there at all) to it. So unless I could add on to your equation to tell it to go 20, ect., cells further down in column B for one or more of the options. I'm not sure how to get away from using column A
 
Upvote 0

Forum statistics

Threads
1,215,263
Messages
6,123,958
Members
449,135
Latest member
jcschafer209

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top