# Formula - rank numbers high to low

#### psrs0810

##### Well-known Member
I have a listing of values and I need to place a number 1-10 for the top 10 highest numbers.
For example, out of these 16 numbers I need a 1 next to the highest number, a 2 next to the second highest number, etc.

 10,527.65 5,655.68 1,701.93 1,349.18 0.00 573.11 140.86 6,064.77 2,093.60 5,680.65 4,045.40 5,546.32 -1,033.02 -277.55 891.20 25,699.12
<colgroup><col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2560;"> <tbody> </tbody>

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### Eric W

##### MrExcel MVP
Thats just a RANK formula:

#### psrs0810

##### Well-known Member
Thanks - it's Friday afternoon for me

delete.

Last edited:

#### Fluff

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback

#### psrs0810

##### Well-known Member
what if I want to make my Rank formula dynamic?
if I have 12 months of data and I have a cell that shows a particular month, I want to rank that month. but if I change the month, how do I have my formula automatically change columns?
Thanks

#### Eric W

##### MrExcel MVP
How is your data laid out? Do you have 12 columns, one per month? Or do you have 2 columns, 1 with the score, 1 with the month? Or something else?

#### psrs0810

##### Well-known Member
12 columns, one per month

#### Eric W

##### MrExcel MVP
I still have some doubts about how the layout looks, but consider:

Book1
ABCDEFGHIJKLMN
1MarJanFebMarAprMayJunJulAugSepOctNovDec
2710,527.6533.02397.59726.65774.09474.69246.65836.6449.46203.91389.42523.63
3105,655.68984.81262.9583.73900.6719.19482.91559.24468.9404.86160.57136.65
41,701.9369.435.81203.12218.46772.52434.61188.19958.3424.59695.02771.91
521,349.18614.72950.98766.8981.78131.79538.82330.68793.99847.16262.71872.69
66076.15413.53284.75638.54283.53976.48433.07322.0429.44220.76416.61
7573.11338.7913.25450.71636.39543.66975.36771.65533.66589.05825.8150.93
81140.86198.82952.19557.07936.01837.69546.78455.62223.74280.92173.59518
96,064.77135.2950.27327.2460.31404.06987.3679.87792.82544.91947.09427.8
1032,093.60622.32576.97658.59758.6643.79693.28875.44811.33295.23266.29722.26
1185,680.65330.64333.99704.61111.31912.585.21889.758.16501.19512.05490.85
124,045.40523.42261.19806.14367.15500.4774.38394.1443.63869.15901.2178.02
1395,546.32420.04300.1111.74677.75961.01612.94664.88800.59180.09795.51519.39
145-1,033.0278.36501.24662.54819.88984.94921.43656.52167.21311.72620.68542.24
15-277.5538.58122.45281.75128674.96953.14491.62672.53962.91610.27121.96
16891.2982.8736.23428.06224.24818.94306.01577.64369.24475.35173.87805.23
17425,699.1277.36532.22217.06376.79799.09436.24680.11650.94650.54809.2262.46
Sheet1
Cell Formulas
RangeFormula
A2:A17A2=IF(RANK(INDEX(C\$2:N\$17,ROWS(A\$2:A2),MATCH(A\$1,C\$1:N\$1,0)),INDEX(C\$2:N\$17,0,MATCH(A\$1,C\$1:N\$1,0)))>10,"",RANK(INDEX(C\$2:N\$17,ROWS(A\$2:A2),MATCH(A\$1,C\$1:N\$1,0)),INDEX(C\$2:N\$17,0,MATCH(A\$1,C\$1:N\$1,0))))

By changing the value in A1, it will rank the values from the corresponding column.