Rank a category according its value in Excel

Hi reader,

Learn how to identify, automatically, the rank of a category according its value in a table. Let consider the bellow example:

You want to return the category with the 2nd highest "Sum of Total Price". Then, you should use the following function:

• =INDEX(pivot_range;MATCH(LARGE(category_list;k_position);category_list;0);pivot_column), where:
• pivot_range: table range (e.g. range "A4:B6")
• category_list: column with categories (e.g. column "A4:A6")
• k_position: column position (ascending) in the data to return (e.g. variable, cell "E4")
• pivot_column: category_list column number considering the pivot_range (e.g. first column in the pivot_range, use "1") 

Then you will get the result:

• To get descending position, just change LARGE on the formula for a SMALL.

Hope it makes your spreadsheets easier.