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 tried entering your formula exactly as you printed it but, it just ends up as a text string that extends across a number of cells.
I tried taking a snippet of the spreadsheet and posting it here I have sent 6 replies to people like this and none of them have been published so I am trying to figure out how to get you images or a copy of the workbook uploaded so people could try and solve the issue. I am still learning the rules of the community as well.
I guess the images had tags which I haven't figured out how to remove yet. I will keep working on things. Thank you everyone for your replies none have worked yet and I believe it may be because the tables are on two different worksheets in the workbook.
Take care everyone.
It shouldn't matter that the tables are in different worksheets within the same workbook. If you move the cursor over e.g. GameStats[Score] as shown in the screenshot do you see the referenced values {61,57,69} as well in your wookbook and in my attached sample workbook?
Please check if the cell containing the formula is formatted as text:
Can you click in the green triangle of the headers in your dynamic table and check which message is shown?
Those green triangles are saying it is a datavalidation list for those fields. It is no longer one for the player, but we still can't get by the formula coming up as text extending beyond the cell.
I have tried maxifs and sumif in the cell and those work fine. The cell is formatted as general just as all the cells they are referencing are in the GameStats worksheet. I keep hoping the formula would at least return an error and I could work from there, but no joy so far.
Thank you for your help.
I have gotten Countif and Maxifs to work in this worksheet, but xlookup does not seem to want to work. The values are not returned all that is seen in the xlookup is the text cursor.
This is proving to be very frustrating also because more than half of my replies have not been published and they might have helped get further along. I am very confused on what I cannot get published on the site. I have tried attaching images, but they say there are tags that stop it.
Thank you again for your help.
If I move the cursor over GameStats[Score] it just tells me it is the formula bar with a text cursor and does not change no matter how long I hover over it. So I don't see any scores also how could I upload a workbook I can't seem to find that function it may help solve this problem.
Thank you for your help.
