Excel Magic Trick 765: Formula To Sort Top 3, Including Ties

See how to create a series of formulas that will Sort Top 3 values and the record associated with the top three values, Including Ties. See the functions COUNTIF, LARGE, IF, ROW, ROWS, N, INDEX, SMALL. Trick from Aladin at the Mr Excel Message Board.

[excelisfun]

Absolutely beautiful, Espen Rosenquist !!! What a great way to improve this.

[Moskow]

You have no idea how ridiculously helpful this was

[crozon]

OMG. you are a magik man - AWESOME! Thank you. You have no idea how much this has saved time. Can't say that I understand it all but it works.

[excelisfun]

As soon as you do an operation on an array and the function is not programmed to handle an array (functions like INDEX, LOOKUP and SUMPRODUCT are programmed to handle arrays), you must send a signal to Excel that this formula is not an ordinary formula, but instead it is an array formula. The signal you send to Excel is Ctrl + Shift + Enter. After you enter the formula using CSE, Excel signals you by putting curly brackets at either end of the formula. If no CSE, formula yields #VALUE! error.

[excelisfun]

Further, it is the logical_test argument in the IF function that is expecting a single logical test that yields 1 TRUE or FALSE. When we give it $B$2:$B$9=G2, we are giving an argument that expects 1 TRUE / FALSE, more than one TREU FALSE. That is what catapults this into the realm of array formulas. The [value_if_false] argument also contains an array of values. Thus, we must use Ctrl + Shift + Enter or it does not work.

[Whitefinish]

Nice video.
Quite advanced formula.

Could you explain a little bit more why you should use CSE ?

[Al-Ahdal]

Why there is a need to take N inside the IF logical argument? Please advise. Thank you

[redfoxarts]

can someone explain to me what does he mean largest 3rd? Larger than 9?

[espenrosenquist]

Great function logic. However, the count function will return 0 if there are fewer than top n values in the list. If working with lists that have fewer values than those wanted (D2), one can add an IF-condition to the Count cell D4:
I found this very useful working with top n lists of occupations distributed over industries - where some industries might not have more than D2 occupations.

[shakeboi]

Can u help pls.... For the 2nd formula whwn I copy down its wrong.... On first cell is correct

[espenrosenquist]

oh, and sorry: I work in local version where ; is the delimiter rather than,

[logaholics]

only thing, you kept saying your gonna do 1 cell to the always 1 cell to the right that you do lol.