# Finding bottom minimum values

#### bbran19

##### New Member
I use this formula to assign A,B,C,D to the highest value, next highest and so on in Column H based on a list as attached using column C and D as differentiators.

=IF(\$H2=MAX((\$C\$2:\$C\$299=\$C2)*(\$D\$2:\$D\$299=\$D2)*(\$H\$2:\$H\$299)),"A",IF(H2=LARGE((\$D\$2:\$D\$299=\$D2)*(\$H\$2:\$H\$299)*(\$C\$2:\$C\$299=\$C2),2),"B",IF(H2=LARGE((\$D\$2:\$D\$299=\$D2)*(\$H\$2:\$H\$299)*(\$C\$2:\$C\$299=\$C2),3),"C",IF(H2=LARGE((\$D\$2:\$D\$299=\$D2)*(\$H\$2:\$H\$299)*(\$C\$2:\$C\$299=\$C2),4),"D",""))))

But I can not for the life of me workout how to assign A,B,C,D to find the Lowest value, next lowest, and so on in another column.

#### Attachments

• Screenshot 2021-05-20 135707.png
53.5 KB · Views: 8

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

##### Well-known Member
1. Please Update your Acount Details and Save it to We know what version of Excel & OS you used to we help based on.
2. I see you can Find Max And Min Based Only Column C.
AND

##### Well-known Member
if you want Use your formula for Lowest and so on, change all Large within Formula to Small.
This is Another Method Only Based Column C. Press CTRL+SHIFT+ENTER
Book1.xlsx
ABCDEFGHIJ
1RaceValueMaxMin
2573
3591
4552 C
5547 A
6555 D
75100C
8550 B
95117A
105106B
11574
12555 D
13576
14596D
15655 C
166120B
17691
186119C
196122A
20653 B
21660
22694
23683
24656 D
256115D
26686
27640 A
28660
29783
30778
31789
32778
33760 B
34791
357111B
36763 C
37777
38742 A
39770 D
40779
417107C
42799D
437117A
44797
45
Parameters
Cell Formulas
RangeFormula
H2:H44H2=IF(G2=LARGE(IF(\$C\$2:\$C\$44=C2,\$G\$2:\$G\$44),1),"A",IF(G2=LARGE(IF(\$C\$2:\$C\$44=C2,\$G\$2:\$G\$44),2),"B",IF(G2=LARGE(IF(\$C\$2:\$C\$44=C2,\$G\$2:\$G\$44),3),"C",IF(G2=LARGE(IF(\$C\$2:\$C\$44=C2,\$G\$2:\$G\$44),4),"D",""))))
I2:I44I2=IF(G2=SMALL(IF(\$C\$2:\$C\$44=C2,\$G\$2:\$G\$44),1),"A",IF(G2=SMALL(IF(\$C\$2:\$C\$44=C2,\$G\$2:\$G\$44),2),"B",IF(G2=SMALL(IF(\$C\$2:\$C\$44=C2,\$G\$2:\$G\$44),3),"C",IF(G2=SMALL(IF(\$C\$2:\$C\$44=C2,\$G\$2:\$G\$44),4),"D",""))))
Press CTRL+SHIFT+ENTER to enter array formulas.

#### bbran19

##### New Member
Thank you so much. I can not believe I didn't think of this. You saved me hours of work.

#### Peter_SSs

##### MrExcel MVP, Moderator

What about these much shorter ones (& no need for C+S+E)?

But please still update your Account details as requested (click your user name at the top right of the forum)

21 05 20.xlsm
CDEFGHI
1RaceValueMaxMin
2573
3591
4552 C
5547 A
6555 D
75100C
8550 B
95117A
105106B
11574
12555 D
13576
14596D
15655 C
166120B
17691
186119C
196122A
20653 B
21660
22694
23683
24656 D
256115D
26686
27640 A
28660
29783
30778
31789
32778
33760 B
34791
357111B
36763 C
37777
38742 A
39770 D
40779
417107C
42799D
437117A
44797
High Low
Cell Formulas
RangeFormula
H2:H44H2=MID("ABCD",SUMPRODUCT((\$C\$2:\$C\$44=C2)*(G2<\$G\$2:\$G\$44))+1,1)
I2:I44I2=MID("ABCD",SUMPRODUCT((\$C\$2:\$C\$44=C2)*(G2>\$G\$2:\$G\$44))+1,1)

##### Well-known Member
You're Welcome & Thanks for Feedback. I think Peter Solution is very Simpler, Better & Shorter for Your Situation. Also test it.

#### Peter_SSs

##### MrExcel MVP, Moderator

Another option if you are using Microsoft 365.

21 05 20.xlsm
CDEFGHI
1RaceValueMaxMin
2573
3591
4552 C
5547 A
6555 D
75100C
8550 B
95117A
105106B
11574
12555 D
13576
14596D
15655 C
166120B
17691
186119C
196122A
20653 B
21660
22694
23683
24656 D
256115D
26686
27640 A
28660
29783
30778
31789
32778
33760 B
34791
357111B
36763 C
37777
38742 A
39770 D
40779
417107C
42799D
437117A
44797
High Low
Cell Formulas
RangeFormula
H2:H44H2=MID("ABCD",MATCH(G2,SORT(FILTER(G\$2:G\$44,C\$2:C\$44=C2),,-1),0),1)
I2:I44I2=MID("ABCD",MATCH(G2,SORT(FILTER(G\$2:G\$44,C\$2:C\$44=C2)),0),1)

#### bbran19

##### New Member
Wow. Thanks Peter. Brilliant

#### Peter_SSs

##### MrExcel MVP, Moderator
Wow. Thanks Peter. Brilliant
You're welcome.

I notice that you have marked the solution that used Microsoft 365 function so ..
But please still update your Account details as requested (click your user name at the top right of the forum)

Last edited:

Replies
3
Views
178
Replies
1
Views
38
Replies
3
Views
583
Replies
1
Views
50
Replies
0
Views
83

1,141,069
Messages
5,704,111
Members
421,327
Latest member
Msh

### 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