# Formula to select the name associated with lowest value from data set & a random name from the lowest options if tied

In Google Sheets, I have 4 Names associated with 4 data points that update throughout the month. I'd like to auto pick the name associated with the lowest data point. If there are 2, 3 or all 4 that have the same lowest point, I'd then like to select a random name but only from the lowest data points.

Ex Scenario 1 - The formula should select adam from the list.

``````Adam - 2
Bill - 3
Charlie - 4
David - 4
``````

Ex Scenario 2 - The formula should select a random name from the adam, charlie & david only.

``````Adam - 2
Bill - 3
Charlie - 2
David - 2
``````

Ex Scenario 3 - The formula should select a random name from the adam & bill only.

``````Adam - 1
Bill - 1
Charlie - 2
David - 2
``````

Any help in developing a formula would be greatly appreciated!

• Hi, welcome to WebApps. Would you please summarise your research (as mentioned in How to Ask) as well as any work that you did to try and solve this yourself. Would you also describe EXACTLY what is the question - you've described a broad scenario but not a specific problem that you are having. Would you also please provide a sample of your data in table format (is the data in ne cell per row, or two cells per row, as well as an example of how you expect a successful outcome to be displayed. Jul 20 at 1:52

For the solution you could use a long formula in a single cell or 2 much more understandable formulas in 2 different cells. The solution I propose works for any number of names, not just 4. Assuming the names are in column `A2:A` and the scores are in column `B2:B`, this is the long formula:

``````=if(
COUNTA(Filter(A2:A,B2:B=min(B2:B)))=1,Filter(A2:A,B2:B=min(B2:B)),
INDEX(Filter(A2:A,B2:B=min(B2:B)),RANDBETWEEN(1, COUNTA(Filter(A2:A,B2:B=min(B2:B)))))
)
``````

As you can see the formula could be simplified if we used another cell with the formula:

``````=Filter(A2:A,B2:B=min(B2:B))
``````

If the latter were put in cell `F2`, the solution would be given by the formula:

``````=if(COUNTA(F2:F)=1,F2:F,INDEX(F2:F,RANDBETWEEN(1, COUNTA(F2:F))))
``````
• Thank you Daniele! This is wonderful and so very much appreciated! Jul 20 at 7:19