Find highest duplicate and vlookup those duplicate.

I know you've tried to be clear, but I am horribly confused. I assume that the values in the green array are not 5A, 5B, etc, but something else. Otherwise there would be no need to look at the green array. You could get 5A and 5B from the yellow grid.

I also assume that Excel 2020 is a Mac thing. I hope that this means you have access to TEXTJOIN.

Finally, I am sure that I have HUGELY overcomplicated this, but we'll see!!

Formula:

Please Login or Register  to view this content.

Hi Glenn, sorry I don't have excel 2020, I meant 365 for windows. I'm curious to know the logic behind the rule? Does it look for the highest possible duplicates first?

Hi.

1. Please amend your profile to show O365, as solutions are very version-specific.

2. The salmon pink box whows the steps I went through to reach a solution. You can delete it. I left it there as an aide memoire, in case I needed to unpick the formula. The bit in H2: =AGGREGATE(14,6,B2:E2/(COUNTIF(B2:E2,B2:E2)>1),1) identifies the highest duplicate value.

3. In the event that there are NO duplicates, it currently returns an ugly error message. Wrapping it in an IFERROR makes it return a blank, instead. See sheet.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

Please try

=TEXTJOIN(" ",,REPT(B6:E10,(MAX(MODE.MULT(B2:E2))=B2:E2)*(A6:A10=MAX(MODE.MULT(B2:E2)))))