JOEYSCLEE
03-02-2017, 01:32 AM
Hi, there
I used below Formula with SMALL Function to look up the Lowest Value and Header Name without Zero. Unfortunately, it did not not work for looking up the 2nd Smallest Value and Header Name without Zero and 3rd Smallest and so on as per below Formula.
Enclosed the attachment for your reviewing. Please help to revise it.
Style
AAA
CCC
BBB
EEE
Lowest Value without Zero
Header Name with Lowest Value without Zero
2nd smallest number without Zero
2nd smallest Header Name without Zero
D01
0.00
0.00
18.00
0.00
18.00
BBB
D15
3.00
18.00
9.00
7.00
3.00
AAA
7.00
EEE
D05
21.00
0.00
16.00
0.00
16.00
BBB
21.00
AAA
D11
7.00
16.00
0.00
9.00
7.00
AAA
9.00
EEE
D17
11.00
18.00
5.00
5.00
5.00
BBB
5.00
BBB
D111
23.00
1.00
3.00
1.00
1.00
CCC
1.00
CCC
D103
21.00
34.00
23.00
20.00
20.00
EEE
21.00
AAA
2nd smallest number without Zero
Cell K6 Formula: IFERROR((SMALL($B6:$E6,COUNTIF($B6:$E6,0)+2))," ")
Cell L6 Formula: IFERROR((INDEX($B$1:$E$1,MATCH(SMALL($B6:$E6,COUNTIF($B6:$E6,0)+2),$B6:$E6, 0)))," ")
I used below Formula with SMALL Function to look up the Lowest Value and Header Name without Zero. Unfortunately, it did not not work for looking up the 2nd Smallest Value and Header Name without Zero and 3rd Smallest and so on as per below Formula.
Enclosed the attachment for your reviewing. Please help to revise it.
Style
AAA
CCC
BBB
EEE
Lowest Value without Zero
Header Name with Lowest Value without Zero
2nd smallest number without Zero
2nd smallest Header Name without Zero
D01
0.00
0.00
18.00
0.00
18.00
BBB
D15
3.00
18.00
9.00
7.00
3.00
AAA
7.00
EEE
D05
21.00
0.00
16.00
0.00
16.00
BBB
21.00
AAA
D11
7.00
16.00
0.00
9.00
7.00
AAA
9.00
EEE
D17
11.00
18.00
5.00
5.00
5.00
BBB
5.00
BBB
D111
23.00
1.00
3.00
1.00
1.00
CCC
1.00
CCC
D103
21.00
34.00
23.00
20.00
20.00
EEE
21.00
AAA
2nd smallest number without Zero
Cell K6 Formula: IFERROR((SMALL($B6:$E6,COUNTIF($B6:$E6,0)+2))," ")
Cell L6 Formula: IFERROR((INDEX($B$1:$E$1,MATCH(SMALL($B6:$E6,COUNTIF($B6:$E6,0)+2),$B6:$E6, 0)))," ")