Search code examples
arraysgoogle-sheetsarray-formulasgoogle-sheets-querygs-vlookup

The query returns values from column D, but there cannot be two values that have written "G" in column B, how to proceed?

On this page (Presence) is Query, said in the question:

=QUERY(D4:H, "select D order by H desc limit 11", 0)

enter image description here

Already on this page (Name And Position) are the id's and the positions of the players:

enter image description here

As a rule, there can only be one goalkeeper playing on a team, so I need Query when defining the 11 players to be aware that if there is more than 1 goalkeeper (G) among them, the goalkeeper below the list needs to leave to be added. the next player on the list created by Query

Link To Spreadsheet

Solution

  • try:

    =ARRAYFORMULA(QUERY({SORT({D4:D, H4:H}, 2, 0), 
       IF(IFERROR(VLOOKUP(INDEX(SORT({D4:D, H4:H}, 2, 0),,1), 'Name And Position'!A4:B, 2, 0))="G", 
 COUNTIFS(IFERROR(VLOOKUP(INDEX(SORT({D4:D, H4:H}, 2, 0),,1), 'Name And Position'!A4:B, 2, 0)), 
          IFERROR(VLOOKUP(INDEX(SORT({D4:D, H4:H}, 2, 0),,1), 'Name And Position'!A4:B, 2, 0)), 
          ROW(K4:K), "<="&ROW(K4:K)), 1)}, 
 "select Col1 where Col3 = 1 order by Col2 desc limit 11", 0))

    0