XLOOKUP not working for multiple criteria

Hello Sturmrider​,

Your formula is not working because you are trying to match the high score and multiple criteria inside XLOOKUP at the same time. This creates multiple matches and XLOOKUP returns the first one it finds, which is not reliable.

Use this formula instead:

=LET(
scores, FILTER(GameStats[Score], (GameStats[Board Game]=[@[Board Game]])(GameStats[Board / Scenario]=[@[Board / Scenario]])),
players, FILTER(GameStats[Player], (GameStats[Board Game]=[@[Board Game]])(GameStats[Board / Scenario]=[@[Board / Scenario]])),
XLOOKUP(MAX(scores), scores, players)
)

This filters the data to the selected board game and scenario, finds the highest score in that subset, and returns the player for that score.

If more than one player has the same high score, it returns the first match.