Finding the category with the highest/lowest score

I had a few people take a personality quiz and then recorded their scores. I want to be able to use a formula that tells you what category they scored the highest, lowest, second highest, and second lowest. Any ideas?

Originally Posted by TMShucks

B24: =LARGE(B$8:B$23,ROW($A1)) and copy down one cell. Drag across.

B26: =SMALL(B$8:B$23,2) drag across
B27: =SMALL(B$8:B$23,1) drag across

Regards, TMS

That would return the highest 4 scores, not the cetegories.

NB: these formula must be array confirmed. Enter the formula into the cell, then hold down Shift and Ctrl, then press enter. If done correctly the formula will appear in the formula bar wrapped in curly braces {} do not try to enter these manually.

In B24, copy down to B25, then right to K25

=INDEX($A$8:$A$23,MATCH(LARGE(B$8:B$23+(ROW($A$8:$A$23)/10000),--LEFT($A24)),B$8:B$23+(ROW($A$8:$A$23)/10000),0))

In B26, copy down to B27, then right to K27

=INDEX($A$8:$A$23,MATCH(SMALL(B$8:B$23+(ROW($A$8:$A$23)/10000),--LEFT($A24)),B$8:B$23+(ROW($A$8:$A$23)/10000),0))

Note that in the event of ties for 2nd highest / lowest, the result returned will be the category closest to the bottom of the table.