Returning Column Headers for Max Values with Ties

[JHansee]

Hello, first thread here. If more information is needed, please let me know.

I would like to pull the highest month of sales in a particular row, which I believe I can manage. The kicker is that some months match others. In this case, I would like to pull all the months that match the highest value (in adjacent cells). I was using an INDEX function with a LARGE function within it, but when there are ties, it pulls the same month's column header twice.

Can someone please take a look and let me know if there is a "simple" way to do this?

Thank you!

[Phuocam]

Try this ...

=INDEX($B$1:$M$1,MATCH(LARGE($B2:$M2-0.0000001*COLUMN($B2:$M2),COLUMNS($O$1:O$1)),
$B2:$M2-0.0000001*COLUMN($B2:$M2),0))

Enter with Ctrl+Shift+Enter.

[JohnTopley]

Try

=IFERROR(INDEX($B$1:$M$1,SMALL(IF($B2:$M2=MAX($B2:$M2),COLUMN($B$1:$M$1)-COLUMN($B$1)+1,""),COLUMNS($B1:B1))),"")

Enter with Ctrl+Shift+Enter

copy across and down

[JHansee]

Both of these worked, thank you both for the help!

[JohnTopley]

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

[Richard Buttrey]

Hi,

If you're prepared to hold the data in a slightly different way then a Pivoot Table will avoid the need for formulae and arguably be far more flexible and useful.

See attached where I've also added a couple of Slicers which allow you to further filter the PT