Looking for the INDEX of 1st and 2nd largest values of a give MONTH

[qadeerahmed]

hi,

I am struggling with formula to return the index of 1st largest and 2nd largest value of given month.

I'm using this array formula to get 1^st and 2^nd largest values

Please Login or Register  to view this content.

and this formula to get the index of

Please Login or Register  to view this content.

but this formula is not return correct index of 1st and 2nd largest values from given month.

If you change month to
JAN: its returning 1^st largest value as 'product2' from column D , But it should return 'product8' which is 13 from column B i.e. JAN
FEB: it is returning 1^st largest value as 'product3 from column B i.e. JAN month, but it should return 'product5' which is 11 from column C i.e. FEB
and so on...

I'm attaching file for better understanding...

Thanks!

[FDibbins]

Quick question - do you have repeat Names in col A in your real data?
If not (as your data shows), then how can you get the 2nd largest?

This will give you the largest for the given month...
=LARGE(INDEX($B$2:$M$13,,MATCH($O$1,$B$1:$M$1,0)),1)
and this will give the name...
=INDEX(A2:A13,MATCH(LARGE(INDEX($B$2:$M$13,,MATCH($O$1,$B$1:$M$1,0)),1),INDEX($B$2:$M$13,,MATCH($O$1,$B$1:$M$1,0)),0))

[qadeerahmed]

Thanks FDibbins

No, I don't have repeated Names in col A, but I also need to get the name of 2nd largest number for the given month...

[FDibbins]

OK you can adapt my suggestions to that requirement, just change the ,1 in the LARGE function to ,2

[qadeerahmed]

Yes, FDibbins, I did! BUT

I'm getting same name twice because of duplicate or same number in months columns, how to handle this condition?

[FDibbins]

Ok try this...
Largest...
=INDEX($A$2:$A$13,MATCH(Q2,INDEX($B$2:$M$13,,MATCH($O$1,$B$1:$M$1,0)),0))
2nd largest...
=IF(Q2=Q3,INDEX(A:A,SMALL(IF($B$2:$B$13=Q3,ROW($A$2:$A$13)),ROWS($A$1:A2))),INDEX($A$2:$A$13,MATCH(Q3,INDEX($B$2:$M$13,,MATCH($O$1,$B$1:$M$1,0)),0)))

[FDibbins]

Note that 2nd formula needs to be ARRAY entered using CSE

[qadeerahmed]

Thank you FDibbins,

BUT

it is working only for month of JAN column, for other columns it is throwing #NUM error for 2nd largest

[Phuocam]

Or try this for the 2nd largest

=IF(Q3<>0,INDEX($A:$A,SMALL(IF(INDEX($B$2:$M$13,0,MATCH($O$1,$B$1:$M$1,0))=Q3,ROW($A$2:$A$13)),COUNTIF(Q$2:Q3,Q3))),"")

Ctrl+Shift+Enter.

[qadeerahmed]

YES,
thanks Phuocam, its working as intended.

Thank you both of you, Phuocam and FDibbins!

Saved my day!