# High and low score benchmarking query in Google Sheets

I am trying to query the highest and lowest scoring colour within a group. Then to query what the benchmark score is for those colours in all groups. I'm totally stuck on an approach.

I have marked in red in the 2nd tab the expected values: https://docs.google.com/spreadsheets/d/1H7Ighd3fHUc3fJgAXWiqx_Ptl51OvZwXMMToczYnx8A/edit?usp=sharing

• What should happen with ties (as would have occurred with Orange and Purple if Beige had been lower than 8.6)? Commented Apr 5, 2022 at 16:06
• contrary to what you wrote in red, it shouldn't be: minimum of "all groups" = 7.6 ? (blue color) Commented Apr 5, 2022 at 16:48
• @Daniele, I think the OP means to retrieve the score from the first and second sheet that matches only the top score from Group A and the bottom score from Group A. Commented Apr 5, 2022 at 17:01

See my added sheet ("Erik Help").

In F2:

`=TRANSPOSE(SORT(SORTN(A2:A,1,1,B2:B,0)))`

In F8:

`=TRANSPOSE(SORT(SORTN(A2:A,1,1,B2:B,1)))`

In F6:

`=ArrayFormula(IF(F2:J2="",,IFERROR(VLOOKUP(F2:J2,'All Groups'!A:B,2,FALSE),"Not Found")))`

In F12:

`=ArrayFormula(IF(F8:J8="",,IFERROR(VLOOKUP(F8:J8,'All Groups'!A:B,2,FALSE),"Not Found")))`

Note that the formulas for F2 and F8 are quite similar. Likewise, the formulas for F6 and F12 are quite similar.

Note also that I restructured so ties can file rightward (up to 5). I changed one value so that you could see the results of a tie.

In addition, I applied a custom CF rule to E:J to highlight the Top and Bottom results rows through Col J.