to find the value next to the maximum value [SOLVED]

[ajaar]

Hi Friends,
I stuck in below situation, can any one tell me how to go ahead.

in A5:A100 i have some text.

in D5:D100 i have numbers.

in P1, i wanted to bring text from A5:A100 which is against maximum value in D5:D100.

Regards
ajryap

 

[Faseeh]

Please try:

=INDEX($A$5:$A$100,MATCH(LARGE(D$5:D$100+1/(ROW(A$5:A$100))^99,ROW(A1)),D$5:D$100+1/(ROW(A$5:A$100))^99,0),0)

Press ctrl+Shift+enter to run.

 

[Mike H..]

Hi Friends,
I stuck in below situation, can any one tell me how to go ahead.

in A5:A100 i have some text.

in D5:D100 i have numbers.

in P1, i wanted to bring text from A5:A100 which is against maximum value in D5:D100.

Regards
ajryap

Hi,

Try this

=INDEX(A5:A100,MATCH(MAX(D5:D100),D5:D100,0))

or for a draggable formula to get the max, second max etc.

=INDEX($A$5:$A$100,MATCH(LARGE($D$5:$D$100,ROW(A1)),$D$5:$D$100,0))

 

[ajaar]

Hi,

Thanks Mike and Faseeh. it works fantastic thank you so much.

Regards
ajaar

 

[Somendra Misra]

Hi ajaar,

Just in case if you have duplicates in column D, try below array formula in P1 and copy down to get all the text pertaining to Max value in column D.

=INDEX($A$5:$A$13,SMALL(IF(MAX($D$5:$D$13)=$D$5:$D$13,ROW($D$5:$D$13)-ROW($D$5)+1),ROWS(P$11)))

Enter with Ctrl+Shift+Enter.

Regards,