Forum Discussion
XLOOKUP not working for multiple criteria
Hi all,
I am setting up a workbook for boardgames played for someone and he wants to see the high score returned for each game. I have tried xlookup in multiple ways and have had no success.
Here is an image of the first worksheet which shows the formula in the cell I have had no success in any form with xlookup so far.
This is the worksheet I am referencing:
Any suggestions or insights into what I am doing wrong will be appreciated here is the formula written out so you don't have to try and parse it from the image.
=XLOOKUP(1, ([High Score]=GameStats[Score])*(GameStats[Board Game]=[Board Game])*(GameStats[Board / Scenario]=[Board / Scenario]),GameStats[Player],,0,1)
Thank you for your help.
Regards,
Larry
5 Replies
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.
Your original function returns the intended result. My screenshot shows the translation of your original function for german Excel. If either of the tables isn't formatted as a dynamic table it doesn't work. Your screenshots show that you are working with dynamic tables. The left table in my screenshot and sample file must be named "GameStats". I assume your referenced dynamic table is named "GameStats".
Then perhaps you've activated "Show formulas".
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.
I seem to have gotten closer with this as the function:
=XLOOKUP(1,(CompiledGS[@[High Score]],GameStats[Score])*(CompiledGS[@[Board Game]],GameStats[Board Game])*(GameStats[Board / Scenario],CompiledGS[@[Board / Scenario]]),GameStats[@Player],,0,1)
It now returns #VALUE!
I have at least got it to return something in the cell. Now to see if I can get it to do what I want.
Hoping someone has an answer for this one for me.
Take care and have a nice day.
That formula does not work in the table only in cells outside.
