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
22 Replies
So far nothing has worked for this xlookup. I tested the cell with other formulas such as countif, or maxifs and they work fine.
Any xlookup formula I put in there though ends up with text spread across the cells. The cell is formatted as general which the cells in Game Stats are formatted as well because I know one error that can occur is if the cells are not formatted the same. I just have not been able to figure out what will work here the Let formulas don't work I am at a loss as to why this ends up as just text streaming across cells and doesn't return anything when others can make it work. Any further help with this problem will be appreciated.
It is probably something very simple does not seem to be working for me. I am frustrated because half of my replies don't show up and I'm trying to figure why that is.
I made sure the cells were the same format I tried both General and Text it doesn't matter it only shows the string for the formula. When I hover over the score it just becomes a text cursor something isn't working I wish I could get this to someone who could look at it and probably have the answer in a minute, I am hoping this reply shows up, but am not optimistic.
It hasn't mattered what formula I've used for this it is not working. I have a Countif and a Maxifs formula working in other cells, but no joy with this one.
Hi NikolinoDE
I have had no luck replying to you directly so I am trying a different method. Unfortunately your solution hasn't worked here is what it looks like:
It may be because the tables are on two different worksheets in the workbook, but I can't even get the formulas to produce an error just the text from the formula.
I haven't been able to figure out how to upload a workbook because I would do that in a second figuring someone might have more luck with it then I do.
Thank you for your help.
For me it works if you use same-row references inside the table you are writing the formula into:
=XLOOKUP(1, ([@[High Score]]=GameStats[Score])*(GameStats[Board Game]=[@[Board Game]])*(GameStats[Board / Scenario]=[@[Board / Scenario]]),GameStats[Player],,0,1)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.
I am hoping this reply shows up I made six yesterday and none of them have appeared. Here is what happened when i entered your formula into the worksheet:
I am wondering if it is because the Game Stats are on a different worksheet. I have been trying to figure out how to upload the workbook, but I see ways to put a link in for a url or a method to upload media I must be missing something because I figured that would be the easiest way for someone to figure it out. Thank you for your help I will keep trying and maybe the moderators will let this one post.
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".
I am wondering if it is because I am working with two different worksheets in the workbook. I am trying to figure out how to upload a workbook as I think that will make everyone's job easier, but I can't seem to find that function on this site.
Thank you for your help though I know it works on a single worksheet. Take care.
I am wondering if it is because the formula is addressing two different worksheets. I'm not sure thank you though for your reply. I am also trying to figure out how to upload a workbook for someone to see if they can find the solution.
Thank you again. Take care.
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?
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.
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.
