Forum Discussion

Iceberg's avatar
Iceberg
Occasional Reader
May 01, 2026
Solved

Identifying the highest values from multiple sources

I'm working on putting together seasonal statistics from a sporting league (Cricket).  One of the stats I need is each player's highest individual score from the season, but with more than 300 playe...
excel
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    May 01, 2026

    Transform your scores data into a structured table and apply this formula. Let's say you call it "Table1". Make sure it's sorted (Player, ascending order and Score and Not Out both in descending order.

    =LET(
players, UNIQUE(Table1[Player]),
score, XLOOKUP(players,Table1[Player],Table1[Scores]),
notout, XLOOKUP(players,Table1[Player],Table1[Not Out]),
HSTACK(players, score, notout)
)

     

IlirU's avatar
IlirU
Iron Contributor
May 02, 2026

Hi Iceberg​,

Try this formula:

=LET(
     gb, GROUPBY(A2:A42, B2:B42, MAX,, 0),
         VSTACK(A1:C1, HSTACK(gb, BYROW(gb,
         LAMBDA(a, XLOOKUP(TRUE, BYROW(a = A2:B42, AND), C2:C42)))))
)

HTH.

IlirU