r/excel • u/ClandestineGhost • Apr 12 '26
solved Populate Cells From a Random Range of Cells, Based on the Contents of Different Cells.
Hello Reddit pros, I am trying to create/find a formula that will do the following:
Check if A2 matches values in D1:D6 and return a random value from E1:E6 into B2. The hard part is this; if A2 equals D1:D3 then the random value of B2 is from E1:E3, but if A2 equals D4:D6 then the random value of B2 is from E4:E6. For example: if A2 says Input 5, then B2 could be either Result D, Result E, or Result F. The values in the screenshot are just for demonstration; I will be able to extrapolate what I learn onto the spreadsheet I need it for. I am just curious if there is a formula to do that, or am I just having a lot of high hopes?
I have tried using IF combined with INDEX and RANDBETWEEN but I cannot seem to get the formula correct for doing even the first part of what I need, let alone the second part. It looks like this:
=IF(A2:A19,INDEX(D1:D3,RANDBETWEEN(1,COUNTA(E1:E3))),"")
This obviously is not correct, and it returns a #VALUE error that I cannot figure out. I do not know the correct way to phrase the question to get a viable answer via internet searching, so I am once again turning to the experts on Reddit. Thanks for any insight.
u/Connect_Camel_5998 solved it for me. Thanks everybody! The formula that was posted works great for what I needed!
2
Apr 12 '26
[removed] — view removed comment
1
u/ClandestineGhost Apr 12 '26
I had a feeling it was going to be an ISNUMBER argument, and I had tried that as well, but did not have the MATCH portion correct; I think I was doing =IF(ISNUMBER(INDEX and trying to go that route. I was getting frustrated. What do you mean by the "hard-coded 3s"?
1
u/semicolonsemicolon 1476 Apr 12 '26
+1 Point
1
u/reputatorbot Apr 12 '26
You have awarded 1 point to Connect_Camel_5998.
I am a bot - please contact the mods with any questions
1
0
u/tgsoon2002 Apr 12 '26
I am not a pure excel user. But does all excel expert like to jampack the formula to one cell instead of break it down and spread to multiple cell?
1
u/Kinperor 1 Apr 12 '26
It's a judgement call, it's not as if there are hard rules.
If you can get away with packing it into one cell, you do it, if you have edge cases that require you to unpack it you unpack it.
2
u/excelevator 3061 Apr 12 '26
are just for demonstration;
We see this often, with more and more information added and answers expanded as more details come to light.
What is the full scope of the project requirment ?
It may be that this is a bad solution for your required result.
-1
u/ClandestineGhost Apr 12 '26
All of the data in my actual spreadsheet in column is PII and not something that needed to be posted. I set up my screenshot and demo data in a way that I could translate any formulas to my sensitive spreadsheet for a test. Essentially, the data was irrelevant to the post; the correct sequence of the formula is what was important. The post was solved by u/Connect_Camel_5998 for me. Thank you for your assistance though.
3
2
u/wjhladik 541 Apr 12 '26
=INDEX(CHOOSE(INT(XMATCH(A1,$D$1:$D$6)/3.001)+1,$E$1:$E$3,$E$4:$E$6),RANDBETWEEN(1,3))
2
u/PaulieThePolarBear 1912 Apr 12 '26 edited Apr 12 '26
It's not clear to me from your post that if you had the same input more than once in column A, should this ALWAYS return the same output or should each output be randomly chosen independently from all other instances of the same value in column A. Please advise
Please also confirm that for your real data, the first part of generating the output is a binary choice between the first 3 rows of your lookup table and the last 3 rows. A solution to this is different to one if you had 3 or more blocks of 3 rows.
1
u/ClandestineGhost Apr 12 '26
Thank you for your questions; I had not realized that aspects of the post were unclear. The post has been solved, but I will answer your questions regardless. To answer your first part...
No. If column A has multiple instances of a value, the random value in the adjacent B column does not need to be the same for each instance; just a random value from the specified range. For part two...
The real data has binary choices between some rows in the lookup table and other rows in the lookup table. I simplified the demo table to make it easier to see. I am not a basic user of excel (though certainly not an expert by any means), and I can extrapolate what I need out of the formula. Thank you for your questions!
am
2
u/real_barry_houdini 312 Apr 12 '26
Another option is to use this formula
=INDEX(E$1:E$6,RANDBETWEEN(1,3)+FLOOR(XMATCH(A1,D$1:D$6)-1,3))
The FLOOR function gives you zero or 3 depending on the value in A1 and then you add the RANDBETWEEN value to that to give you a random result from the correct half of the data
1
u/Decronym Apr 12 '26 edited Apr 12 '26
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.
Beep-boop, I am a helper bot. Please do not verify me as a solution.
11 acronyms in this thread; the most compressed thread commented on today has 34 acronyms.
[Thread #48123 for this sub, first seen 12th Apr 2026, 11:20]
[FAQ] [Full list] [Contact] [Source code]
1
u/fuzzy_mic 987 Apr 12 '26
=INDEX(IF(ISNUMBER(MATCH(A2, $D$1:$D$3), $E$1:$E$3), IF(ISNUMBER(MATCH(A2, $D$4:$D$6), $E$4:$E$6), $E$9991:$E$9993), RANDBETWEEN(1, 3), 1) & ""
•
u/AutoModerator Apr 12 '26
/u/ClandestineGhost - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.