# Formula to randomly select the name associated with lowest value from data set & a random from the lowest options if tied with drop-down select filter

I have a data set on Google Sheets where for example,

Name Scores Region State
First Row Row Row
Second Row Row Row

On another tab, I have a drop-down list to select a specific Region and State. I am looking for a formula that apply these drop-down filters and select the name associated with the lowest score and if there are multiple names that are tied, randomly select from those names.

Ex Scenario 1 - The formula should randomly select Adam when the drop-downs selected are East and New York.

Name Scores Region State
Bill 3 West California
Charlie 4 East New York
David 4 West Seattle
Steve 2 West California

Ex Scenario 2 - The formula should select a random name from Adam and Charlie when the drop-downs selected are East and New York.

Name Scores Region State
Bill 3 West California
Charlie 2 East New York
David 2 West Seattle
Steve 2 West California

Ex Scenario 3 - The formula should select a random name from Bill and Steve when the drop-downs selected are West and California

Name Scores Region State
Bill 2 West California
Charlie 2 East New York
David 5 West Seattle
Steve 2 West California

Any help would be appreciated!

• Welcome to Web Applications Stack Exchange. Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. Commented Jan 25, 2023 at 20:46
• Removed leading spaces from your table markup so that the data gets shown as tables. If the answer below does not solve your problem, please comment below the answer. Commented Jan 25, 2023 at 23:58

Use `sortn()` and `filter()`, like this:

``````=lambda(
area, city,
lambda(
names, scores,
sortn(names, 1, 0, scores, true, randarray(rows(scores)), true)
)(
filter(Data!A2:A, Data!C2:C = area, Data!D2:D = city),
filter(Data!B2:B, Data!C2:C = area, Data!D2:D = city)
)
)(
"East", "New York"
)
``````

Replace `"East"` and `"New York"` with references to the pertinent drop-down list cells, and `Data` with the name of your data sheet.

• Thank you doubleunary! This works. Very much appreciated. Commented Jan 26, 2023 at 19:07