X Lookup returning multiple matches

[LizM1980]

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?

[#2]

@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.

[#3]

@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?

[#4]

@LizM1980 Why not share a workbook that accurately represents what you want to achieve. Then I/we don't have to guess.

[#5]

@Riny_van_Eekelen sorry, I have now added other relevant data. hopefully this helps

[#6 · Accepted solution]

@LizM1980 

I would change the structure and then do a pivot table.

But I also included a formula solution.

[#7]

@LizM1980 Thanks! Not sure I understand what I'm looking at, but I would begin by rearranging the data. Rather than having two rows per person and merged cells, I prefer to use a structured table with one row per person. One column for the name, followed by 5 columns with numbers between 1 and 10, followed by 5 columns that lookup the correct values from another structured table. Then a TOTAL for the 5 lookup results and then a column for the Ranking, sorted Smallest to Largest. Now you can use the first 10 rows for a Top 10 listing.

[#8]

oh yeah, of course. Why didn't I think of that! Thankyou