Breaking a tie in a large function

[Devge]

I am doing an index match with a large formula, however I have duplicates values in AT1-BE1


=INDEX($AT$1:$BE$1,MATCH(LARGE($AT2:$BE2,COLUMNS($BF2:BF2)),$AT2:$BE2,0)) Is there a way to show the values that have duplicates?

Thanks!

 

[Eric W]

Are the values in AT2:BE2 integers? If so, you can use the column number to break the tie:

=INDEX($AT$1:$BE$1,MATCH(LARGE($AT2:$BE2+COLUMN($AT2:$BE2)/1000,COLUMNS($BF2:BF2)),$AT2:$BE2+COLUMN($AT2:$BE2)/1000,0))
confirmed by pressing Control+Shift+Enter.

If the values are arbitrary fractional numbers, there are more complicated ways to do it.

 

[Devge]

I had not even thought of using the column number to break ties, Eric, thank you very much I will try this and get back to you!!

 

[Eric W]

One more to try: this one doesn't require the values to be integers, but it's a bit longer and still requires the CSE:

=INDEX($AT$1:$BE$1,SMALL(IF($AT2:$BE2=LARGE($AT2:$BE2,COLUMNS($BF2:BF2)),COLUMN($AT2:$BE2)-COLUMN($AT2)+1),COLUMNS($AT2:AT2)-COUNTIF($AT2:$BE2,">"&LARGE($AT2:$BE2,COLUMNS($BF2:BF2)))))