Originally Posted by
tigeravatar
amahindroo,
Using your sample file, give this formula a try:
=INDEX(LARGE(INDEX((D3:D9>=C14)*(D3:D9<=D14)*D3:D9,),ROW(INDIRECT("1:"&COUNTIFS(D3:D9,">="&C14,D3:D9,"<="&D14)-1))),MATCH(MAX(INDEX(LARGE(INDEX((D3:D9>=C14)*(D3:D9<=D14)*D3:D9,),ROW(INDIRECT("1:"&COUNTIFS(D3:D9,">="&C14,D3:D9,"<="&D14)-1)))-LARGE(INDEX((D3:D9>=C14)*(D3:D9<=D14)*D3:D9,),ROW(INDIRECT("2:"&COUNTIFS(D3:D9,">="&C14,D3:D9,"<="&D14)))),)),INDEX(LARGE(INDEX((D3:D9>=C14)*(D3:D9<=D14)*D3:D9,),ROW(INDIRECT("1:"&COUNTIFS(D3:D9,">="&C14,D3:D9,"<="&D14)-1)))-LARGE(INDEX((D3:D9>=C14)*(D3:D9<=D14)*D3:D9,),ROW(INDIRECT("2:"&COUNTIFS(D3:D9,">="&C14,D3:D9,"<="&D14)))),),0))
Bookmarks