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.
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.
My replies don't seem to be showing up, but I entered your formula as presented and this is what I got:
It may be because I am working with two different work sheets in the workbook. I would like to upload it but, have yet to find that function on the site.
I'm hoping this can be solved if not I will keep working on it. Thank you for your help.
Hi,
I entered your formula as you sent it to me this is what I got:
I am wondering if it is because I am using multiple worksheets. I have also been trying to figure out how you upload a work book I would do this because there is no personal info on it and this may make it easier to do. If you have any other suggestions please let me know and thank you for your time.
Hi NikolinoDE
I entered your formula in the worksheet and this is what I got:
I am wondering if this is because the tables are on two different worksheets. I wish I could figure out how to upload a workbook to the community. I don't see any thing for attaching a file if you have any ideas I am happy to upload the workbook it has no personal info on it.
Maybe the two different worksheets is the problem, but I don't know. Thank you for your help though and take care.
Regards,
Larry
Hi NikolinoDE,
I have entered the formula exactly as you put it in and this is the result I got:
I have also been trying to figure out how to attach a file to my question, but couldn't find that I figured it would make it easier with the workbook uploaded. If you know how I can do that I will be happy to upload the workbook there is no personal info for anyone on there.
Thank you for your help.
Regards,
L
