0

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
Adam 2 East New York
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
Adam 2 East New York
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
Adam 4 East New York
Bill 2 West California
Charlie 2 East New York
David 5 West Seattle
Steve 2 West California

Any help would be appreciated!

2
  • 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. 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. Jan 25, 2023 at 23:58

1 Answer 1

0

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.