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)
)

     

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
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)
)