Populate Cells From a Random Range of Cells, Based on the Contents of Different Cells.
Our take
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!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- What formula should I be using to populate one column’s data automatically based on my entry for another column?Hi! I need some help here. first off, I’m not allowed to use a v-lookup for this file due to some organisational restrictions, so i’m unable to create a table 2 to facilitate the lookup values. I’m only allowed to use table 1 itself, which is where I have to put in all my values. I have been trying to use index/match instead but can’t see to figure the right formula out. so basically, what I need is for my manual input of data in column J to populate the associated remark in column K. • column J input “NA” = column K input “visit 2” • column J input “nil” = column K input “insufficient blood” • column J input “1” = column K input blank these are the only 3 that I require. let’s say I require it for 30 rows. so my range of rows to populate would be K2:K30 based on the directly adjacent data of J2:J30. how can I do this in formula or automation? thanks in advance! submitted by /u/yanny-jo [link] [comments]
- Drop-down list dependant on two other cellsI have a file where I want a drop-down list in cell D14 that is based on cell D10 and D12. I have looked online and saw lot of videos that are dependant on one cell with the indirect formula, but not two. https://preview.redd.it/ptykf9yzrrwg1.png?width=289&format=png&auto=webp&s=6c0fe00c73c3d45816229bcba5fe2f1733bff083 So for example, if I had this random data and I chose Animal in cell D10, Red in cell D12, I would expect to see 33 and 2 in the drop-down menu of cell D14. Is this possible? The data is in another tab called Data and the info is in column F (F3:F1000) that should appear in the drop-down menu of cell D14. D10 and D12 are both drop-down menus if that matters submitted by /u/LookAtThosePepperoni [link] [comments]
- Change contents of cells based on a drop downSo, I'm trying to generate a spreadsheet to make the forms we use in the office to issue keys. When people are given an apartment, we have a sheet with all the key numbers on it, and then they sign for it. At the moment, we've got 22 different PDFs and Word documents, and it doesn't work. So, what I want to do is this. I've built the template in Excel and created a Reference sheet which will have all the numbers in it. What I am trying to do is set it up so that when you use the Apartment number from a dropdown, it populates with all the key numbers. This sounds like it should be easy to do, but my Excel brain is broken. Advice would be much appreciated! submitted by /u/Hot_Syrup_1774 [link] [comments]
- Am I not using XLOOKUP correctly? And absolutes $shttps://imgur.com/a/o0alvvv I am basically trying to get column "E" (Billing End) on the "Upload sheet" to populate with the values found in column "C" on the "Data sheet"...ONLY IF the values in Data Sheet (B:B) and Upload Sheet (C:C) match AS WELL AS Data Sheet (D:D) and Upload Sheet (B:B) match. Sorry, I could have organized the columns more alphabetically but I figure you'd get the gist of it and I can play around with the figures. In Upload sheet E2 and pulling formula down, I would put... XLOOKUP(1, ('Data sheet'!D$2:D$7=B2)*('Data sheet'!B$2:B$7=C2), 'Data sheet'!C$2:C$7, "" ) submitted by /u/jwnsfw [link] [comments]