Formula Error (Not using VBA Code)

[JOEYSCLEE]

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]

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]

Thanks for reviewing! The 2nd & 3rd smallest value can be located as per your advice.

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]

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]

Thanks for your help!!

[mikerickson]

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]

Thank you for the reply!! It works great!!

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