PDA

View Full Version : [SOLVED] Formula Error (Not using VBA Code)



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)))," ")

onlyadrafter
03-03-2017, 10:23 AM
Hello,

I think this should work, I get then answers you expect (I think) for your sample:

In G6 I have =IFERROR((SMALL($B2:$E2,COUNTIF($B2:$E2,0)+1))," ")

In H6 I have = IFERROR((INDEX($B$1:$E$1,MATCH(SMALL($B2:$E2,COUNTIF($B2:$E2,0)+1),$B2:$E2, 0)))," ")

In I6 I have =IFERROR(IF(SMALL($B2:$E2,COUNTIF($B2:$E2,0)+2)=G2,SMALL($B2:$E2,COUNTIF($B 2:$E2,0)+3),SMALL($B2:$E2,COUNTIF($B2:$E2,0)+2)),"")

In J6 I have =IFERROR((INDEX($B$1:$E$1,MATCH(SMALL($B2:$E2,COUNTIF($B2:$E2,0)+2),$B2:$E2 , 0)))," ")

In K6 I have =IFERROR(IF(SMALL($B2:$E2,COUNTIF($B2:$E2,0)+3)=I2,SMALL($B2:$E2,COUNTIF($B 2:$E2,0)+4),SMALL($B2:$E2,COUNTIF($B2:$E2,0)+3)),"")

In L6 I have = IFERROR((INDEX($B$1:$E$1,MATCH(SMALL($B2:$E2,COUNTIF($B2:$E2,0)+3),$B2:$E2, 0)))," ")

Copy down as far as required.

JOEYSCLEE
03-03-2017, 08:36 PM
Thanks for reviewing! The 2nd & 3rd smallest value can be located as per your advice. :clap:

On the other hand, if I want to know the exact Header of 2 duplicated values with the mentioned formula. How can modify it?

Example : there is same value with the different Headers for style - D17. The formula can only locate the first occurrence of the Header - BBB but the 2nd occurrence of the Header - EEE cannot be shown as per my original attachment.



Style
AAA
CCC
BBB
EEE


D17
11
18
5
5


D111
23
1
3
1

onlyadrafter
03-04-2017, 07:23 AM
Hello,

In H2 I have =IF(G2=$B2,$B$1&" ","")&IF(G2=$C2,$C$1&" ","")&IF(G2=$D2,$D$1&" ","")&IF(G2=$E2,$E$1,"")

Copy this over to J2 and L2, then copy down as far as required.

This gives correct results to your sample, not sure how it would work for all combinations.

JOEYSCLEE
03-04-2017, 10:59 PM
Thanks for your help!!:yes:yes

mikerickson
03-05-2017, 09:53 AM
Try

IFERROR((INDEX($B$1:$E$1, MATCH(SMALL($B6:$E6+COLUMN($B6:$E6)/1000,COUNTIF($B6:$E6,0)+2),$B6:$E6+COLUMN($B6:$E6)/1000, 0)))," ")

JOEYSCLEE
03-06-2017, 09:48 AM
Thank you for the reply!! It works great!!:friends:


Try

IFERROR((INDEX($B$1:$E$1, MATCH(SMALL($B6:$E6+COLUMN($B6:$E6)/1000,COUNTIF($B6:$E6,0)+2),$B6:$E6+COLUMN($B6:$E6)/1000, 0)))," ")