Forum Discussion
X Lookup returning multiple matches
Hi. I'm trying to show a list of placings, i.e. 1st, 2nd, 3rd, etc. but when there are more than 2 with the same amount, it puts that person twice. How can I fix this so it shows the correct placings?
I would change the structure and then do a pivot table.
But I also included a formula solution.
7 Replies
LizM1980 Looking at your formula, I see what you are trying to achieve. But, that's not going to work. For example, the formula in G5 looks like this:
=XLOOKUP(H5,$B$2:$B$15,$A$2:$A$15,"n/a",IFG5=G4,-1)
In the 5th and 6th arguments (bold, red) you are trying to say "if the number in G5 equals G4, then search from the bottom up." But the 5th argument is comparing the value in cell IFG5 (i.e. column IFG, row 5) with that of G4. Since you have no values in column IFG, this will always return FALSE (=0) which tells the function to search for an exact match. The 6th argument -1 triggers the bottom-up search. So, not really what you are trying to achieve.
Even when it had been possible, it would not have worked how you desire. XLOOKUP can search top down or bottom up. That allows you to find either the first or last match in a list, but not any of the ones in between.
You need to make all scores unique for a lookup function to find all matching scores. With a helper column you can add a very small fraction to each score. But when you suppress the decimals the scores will look like the original ones. I've demonstrated that in the attached file.
Riny_van_Eekelen Thanks, but I actually have other data that's adding up to get my "total" so I can't just slightly change the amounts.
Do you know another way I can achieve these placings?
LizM1980 Why not share a workbook that accurately represents what you want to achieve. Then I/we don't have to guess.
