Imagine you have spreadsheet with data in a fixed # of contiguous rows.. let's say row 1 through row 20

Now let's say you have 3 columns of interest.

A, B and C

Column A is a label column.. the data in there are just string labels.. let's say types of canned food.. Tuna, Spam, Sardines, etc.

Column B is our number column.. let's say it is prices. e.g. 2 for Tuna, 5 for Spam and 3 for Sardines. These prices can change often very rapidly.. ok so prices are not the best example but let's imagine that prices change rapidly.

Now Column C is where we want to put the formula.

I would like to have a formula in Column C that will pull the labels from Column A, based on their prices in column B and rank them from highest to lowest.. that is C1 would calculate to "Spam", C2 to "Sardines" and C3 to "Tuna"

right now there are 20 rows of data.. but maybe at some other point there might be 30 or 6 or 40, etc.

So can someone help me out with the formula or at least explain what functions I need to use and the general idea involved? thanks

CodePudding user response：

=IF(A2:A200<>"";SORTBY(A2:A200;B2:B200;-1);"")

CodePudding user response：

You can simply use SORT formula. In this case =SORT(A1:B1000,2,-1) where A1:B1000 is range to be sorted, second parameter 2 is column number from range to sort by, 3rd parameter for order (-1 is desceding). Place formula in C1 and you will get spilled array.