Find second date maximum value

[Berna11]

Good morning I ask for your help to find the date of a maximum value.
In the attached file in M5 I have the following formula:

= INDEX ($ B $ 3: $ K $ 3; COMPARE (MAX (B5: K5); B5: K5; 0))

I find the maximum value in the line and it works if I only have a value.
If I have the same two values as in line 5, how can I change the formula to get the value I handed in cell N5?

the rye can also be combined as in cell P5
Thank you.

[bebo021999]

Try:

Please Login or Register  to view this content.

[shukla.ankur281190]

tRY

Please Login or Register  to view this content.

You can change highlighted party if you required.


...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[tim201110]

=lookup(max(b5:k5),b5:k5,$b$3:$k$3)

[JohnTopley]

Try

in M2

=IFERROR(INDEX($B$3:$K$3,SMALL(IF($B$5:$K$5=MAX($B$5:$K$5),COLUMN($B$3:$K$3)-COLUMN($B$3)+1,""),COLUMNS($B$3:B3))),"")



...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy (drag) across to N2

[davsth]

Entered as an array formula (shift Control Enter) try
=IFERROR(INDEX($B$3:$K$3,SMALL(IF($B5:$K5=MAX($B5:$K5),COLUMN($B$3:$K$3)-COLUMN(INDEX(B5:K5,1))+1),2)),"")

[Phuocam]

Try this in M5:

=IFERROR(INDEX($B$3:$K$3,SMALL(IF($B5:$K5=MAX($B5:$K5),COLUMN($B5:$K5)-1,""),COLUMNS($M$5:M$5))),"")

Enter with Ctrl+Shift+Enter.

[Berna11]

Thank you all for your help
with glass proposed by john Topley
I get all the dates by dragging it to the right with a single formula.
Thank you very much

[Berna11]

I also tried after the solution of Phuocam
with a formula you get all the dates
Thanks again
Next