# SMALL/LARGE with IF

#### MarcelXLS

##### New Member
Hello, I am an Excel beginner and was experimenting for hours with LARGE with a condition IF. Unfortunately, I did not make it..can anyone be of help please?

 A DEFENSIVE 9% B DEFENSIVE 15% C CYCLICAL 8% D DEFENSIVE 12% E DEFENSIVE 10% F CYCLICAL 5% G CYCLICAL 7% H DEFENSIVE 5% I CYCLICAL 6% J CYCLICAL 9%

<tbody>
</tbody>

I would like to rank the A, B, C etc. according to their size (10%, etc.). One ranking for the DEFENSIVE and one for the CYCLICAL. Any assistance? Thank you so much and...HAPPY NEW YEAR!!

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### oldbrewer

##### Well-known Member
Hello, I am an Excel beginner and was experimenting for hours with LARGE with a condition IF. Unfortunately, I did not make it..can anyone be of help please?

 A DEFENSIVE 9% B DEFENSIVE 15% C CYCLICAL 8% D DEFENSIVE 12% E DEFENSIVE 10% F CYCLICAL 5% G CYCLICAL 7% H DEFENSIVE 5% I CYCLICAL 6% J CYCLICAL 9%

<tbody>
</tbody>

I would like to rank the A, B, C etc. according to their size (10%, etc.). One ranking for the DEFENSIVE and one for the CYCLICAL. Any assistance? Thank you so much and...HAPPY NEW YEAR!!
 DEFENSIVE CYCLICAL DEFENSIVE CYCLICAL A DEFENSIVE 9% 1 0.09 0.15 0.09 B DEFENSIVE 15% 2 0.15 0.12 0.08 C CYCLICAL 8% 3 0.08 0.1 0.07 D DEFENSIVE 12% 4 0.12 0.09 0.06 E DEFENSIVE 10% 5 0.1 0.05 0.05 F CYCLICAL 5% 6 0.05 #NUM! #NUM! G CYCLICAL 7% 7 0.07 #NUM! #NUM! H DEFENSIVE 5% 8 0.05 #NUM! #NUM! I CYCLICAL 6% 9 0.06 #NUM! #NUM! J CYCLICAL 9% 10 0.09 #NUM! #NUM! can you not hide helper columns like these to get your desired output ?

<colgroup><col><col><col><col span="2"><col><col><col><col><col><col></colgroup><tbody>
</tbody>

#### FranzV

##### Board Regular
You could also do it in a single column with a COUNTIFS function that will check for rows that have the same Class and a higher value (asuming that #1 is the one with the highest %). I also included an Overall Rank that you can make with the RANK.EQ function but accepts no conditions natively.

[B]Excel 2016 (Windows) 64 bit[/B][TABLE]
[TH][/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[/TR]
[TD="align: center"]2[/TD]
[TD]Item[/TD]
[TD]Class[/TD]
[TD]Value[/TD]
[TD]ClassRank[/TD]
[TD]OverallRank[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]A[/TD]
[TD]DEFENSIVE[/TD]
[TD="align: right"]9%[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]B[/TD]
[TD]DEFENSIVE[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]C[/TD]
[TD]CYCLICAL[/TD]
[TD="align: right"]8%[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]D[/TD]
[TD]DEFENSIVE[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]E[/TD]
[TD]DEFENSIVE[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]F[/TD]
[TD]CYCLICAL[/TD]
[TD="align: right"]5%[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]G[/TD]
[TD]CYCLICAL[/TD]
[TD="align: right"]7%[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]H[/TD]
[TD]DEFENSIVE[/TD]
[TD="align: right"]5%[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD]I[/TD]
[TD]CYCLICAL[/TD]
[TD="align: right"]6%[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD]J[/TD]
[TD]CYCLICAL[/TD]
[TD="align: right"]9%[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD]Item[/TD]
[TD]Class[/TD]
[TD]Value[/TD]
[TD]ClassRank[/TD]
[TD]OverallRank[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD]J[/TD]
[TD]CYCLICAL[/TD]
[TD="align: right"]9%[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD]C[/TD]
[TD]CYCLICAL[/TD]
[TD="align: right"]8%[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD]G[/TD]
[TD]CYCLICAL[/TD]
[TD="align: right"]7%[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD]I[/TD]
[TD]CYCLICAL[/TD]
[TD="align: right"]6%[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD]F[/TD]
[TD]CYCLICAL[/TD]
[TD="align: right"]5%[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD]B[/TD]
[TD]DEFENSIVE[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD]D[/TD]
[TD]DEFENSIVE[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD]E[/TD]
[TD]DEFENSIVE[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD]A[/TD]
[TD]DEFENSIVE[/TD]
[TD="align: right"]9%[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD]H[/TD]
[TD]DEFENSIVE[/TD]
[TD="align: right"]5%[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[/TR]
</tbody>[/TABLE]
[CENTER][COLOR=#161120][B]Hoja1[/B][/COLOR][/CENTER]

[TABLE="width: 85%"]
<tbody>[TR]
[TD][B]Worksheet Formulas[/B][TABLE="width: 100%"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E3[/TH]
[TD]=COUNTIFS([COLOR=#0000FF]\$C\$3:\$C\$12,\$C3,\$D\$3:\$D\$12,">="&\$D3[/COLOR])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F3[/TH]
[TD]=RANK.EQ([COLOR=#0000FF]\$D3,\$D\$3:\$D\$12,0[/COLOR])[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I hope it helps.

#### MarcelXLS

##### New Member
Thank you Oldbrewer. Well, I would like to have something like a leaderbord, e.g. in one table the defensive names descending by size (row 1 = B at 15%, row 2 = D at 12%, etc.). And in another table the same for the cyclical names. Is that possible without five days of coding?

#### FranzV

##### Board Regular

On cells I3 to I7 you can copy the following array formula and press CTRL+SHIFT+ENTER to turn add the curly brackets at both ends and make it work.

 {=INDEX(\$B\$3:\$B\$12,MATCH(\$H3:\$H7,\$E\$3:\$E\$12*(\$C\$3:\$C\$12=I\$2),0))} ​

<tbody>
</tbody>

On cells J3 to J7 you can use this normal formula.

=INDEX(\$D\$3:\$D\$12, MATCH( I3, \$B\$3:\$B\$12, 0 ))

HIJKL
2
RankDefensive%Cyclical%
3
1​
B
15%​
J
9%​
4
2​
D
12%​
C
8%​
5
3​
E
10%​
G
7%​
6
4​
A
9%​
I
6%​
7
5​
H
5%​
F
5%​

<tbody>
</tbody>

Last edited:

#### MarcelXLS

##### New Member

On cells I3 to I7 you can copy the following array formula and press CTRL+SHIFT+ENTER to turn add the curly brackets at both ends and make it work.

 {=INDEX(\$B\$3:\$B\$12,MATCH(\$H3:\$H7,\$E\$3:\$E\$12*(\$C\$3:\$C\$12=I\$2),0))} ​

<tbody>
</tbody>

On cells J3 to J7 you can use this normal formula.

=INDEX(\$D\$3:\$D\$12, MATCH( I3, \$B\$3:\$B\$12, 0 ))

HIJKL
2
RankDefensive%Cyclical%
3
1​
B
15%​
J
9%​
4
2​
D
12%​
C
8%​
5
3​
E
10%​
G
7%​
6
4​
A
9%​
I
6%​
7
5​
H
5%​
F
5%​

<tbody>
</tbody>

Many thanks FranzV, it works! And I should have asked you hours ago!! Have a great day!

Replies
9
Views
373
Replies
0
Views
662
Replies
8
Views
237
Replies
0
Views
103
Replies
0
Views
238

1,191,708
Messages
5,988,234
Members
440,139
Latest member
ngaicuong2017

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back