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 1st and 2nd largest values
Formula:
={LARGE(IF(B1:M1=O1,B2:M13),1)}
and this formula to get the index of
Formula:
=IF(Q2<>0,IF(LEN(Q2),INDEX($A$2:$A$13,SMALL(IF(B$2:M$13=Q2,ROW($1:$12)),COUNTIF(Q$2:Q2,Q2))),""),"")
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 1st 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 1st 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!
Bookmarks