Formula Question

[coachdp3]

=INDEX($BJ$4:$BJ$33,LARGE($BJ$4:$BP$33,3))

I have the above formula for a form I've made and it works as long as the info in BP33 stays in order from 1 - 33. When I change the order I get a "REF" error. Any idea why?

I need excel to find the 3rd largest value in an array. The way its set up above, it works as long as the numbers are in order. What if they are out of order? Thanks

 

[Aladin Akyurek]

=INDEX($BJ$4:$BJ$33,LARGE($BJ$4:$BP$33,3))

I have the above formula for a form I've made and it works as long as the info in BP33 stays in order from 1 - 33. When I change the order I get a "REF" error. Any idea why?

I need excel to find the 3rd largest value in an array. The way its set up above, it works as long as the numbers are in order. What if they are out of order? Thanks

Invoke just:

=LARGE($BJ$4:$BP$33,3)

 

[coachdp3]

Thank you for responding. The formula by itself only returns the 3rd highest number but I need excel to return the other portion of the array.

Example:

The Array:

Column "A" has the names John, Mark, & Chris
Column "B" has the ages of each. 10, 15, & 20

Your formula returns the 3rd highest age (20).
I need the formula to return who is 20 (Chris).

This was why I used =INDEX(

Any ideas?

 

[jasonb75]

Something like

=INDEX($BJ$4:$BJ$33,MATCH(LARGE($BP$4:$BP$33,3),$BP$4:$BP$33,0))

 

[anandvarma]

try this

=INDEX(A1:A5,MATCH(LARGE(B1:B5,3),$B$1:$B$5,0))

 

[Aladin Akyurek]

Thank you for responding. The formula by itself only returns the 3rd highest number but I need excel to return the other portion of the array.

Example:

The Array:

Column "A" has the names John, Mark, & Chris
Column "B" has the ages of each. 10, 15, & 20

Your formula returns the 3rd highest age (20).
I need the formula to return who is 20 (Chris).

This was why I used =INDEX(

Any ideas?

Your INDEX bit was referencing the same range as your LARGE bit. That led me to believe that you were just interested having the 3rd largest value.

Let A2:B5 house:

John
Mark
Chris
Jason

and

10
15
20
10

Note that the 3rd largest value is 10.

D1: 3

D2, just enter:

=COUNTIF(B2:B5,LARGE(B2:B5,D1))

D4, control+shift+enter, not just enter:

=IF(ROWS($D$4:D4)<=$D$2,INDEX($A$2:$A$5,
    SMALL(IF($B$2:$B$5=LARGE($B$2:$B$5,$D$1),
      ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($D$4:D4))),"")