Forum Discussion
XLOOKUP not working for multiple criteria
Here are two possible approaches that might help you.
A good approach can be…
=LET(
currentGame, [@[Board Game]],
currentScenario, [@[Board / Scenario]],
maxScore, MAXIFS(GameStats[Score], GameStats[Board Game], currentGame, GameStats[Board / Scenario], currentScenario),
XLOOKUP(1,
(GameStats[Score] = maxScore) *
(GameStats[Board Game] = currentGame) *
(GameStats[Board / Scenario] = currentScenario),
GameStats[Player], "No Data"))
Why this approach…
- Efficient → MAXIFS avoids scanning everything multiple times
- Readable → LET makes debugging easy
- Stable → Doesn’t depend on column positions
- Scales well as your dataset grows
A good approach if you want to handle ties (multiple winners)
=LET(
currentGame, [@[Board Game]],
currentScenario, [@[Board / Scenario]],
maxScore, MAXIFS(GameStats[Score], GameStats[Board Game], currentGame, GameStats[Board / Scenario], currentScenario),
TEXTJOIN(" / ", TRUE,
FILTER(GameStats[Player],
(GameStats[Score] = maxScore) *
(GameStats[Board Game] = currentGame) *
(GameStats[Board / Scenario] = currentScenario))))
Use this approach if...
You want all winners, not just the first
Example output:
Lance Whalen / Al Amaral
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
Hi NikolinoDE,
I have tried all the methods everyone has shared on this and had no luck. Here is what yours produced in a screenshot:
I have replied to your solution six times and none of the replies have shown up I hope this one gets through otherwise I will start a new discussion. I am trying to figure out how to upload a workbook, but have had no luck so far. The problem may be that these tables are on two different worksheets, but I won't know until one of my replies gets through. I hope you can help. Thank you.