2 min readfrom Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

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.

I am aware the formula on the screenshot is different than my post body; I deleted the top row but didn't fix the formula

u/Connect_Camel_5998 solved it for me. Thanks everybody! The formula that was posted works great for what I needed!

submitted by /u/ClandestineGhost
[link] [comments]

Want to read more?

Check out the full article on the original site

View original article

Tagged with

#Excel alternatives for data analysis
#natural language processing for spreadsheets
#generative AI for data analysis
#AI formula generation techniques
#formula generator
#rows.com
#cloud-based spreadsheet applications
#modern spreadsheet innovations
#machine learning in spreadsheet applications
#enterprise-level spreadsheet solutions
#digital transformation in spreadsheet software
#Excel compatibility
#row zero
#collaborative spreadsheet tools
#AI-driven spreadsheet solutions
#financial modeling with spreadsheets
#automation in spreadsheet workflows
#no-code spreadsheet solutions
#AI-powered spreadsheet
#Excel alternatives