Formula Error for finding smallest values and index with horizontal cells

**JoeyscLee** · 03-05-2017, 03:11 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 for the duplicated values and 3rd Smallest and so on as per below Formula.
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)))," ")
Meanwhile, when the lowest value is same with different Headers, I want to know those exact Header of the duplicated values with the mentioned formula. How can it be modified?
Remark : +1 for 1st smallest value, +2 for the 2nd smallest value, +3 for the 3rd smallest value and so on.
Formula Error.jpg
Formula Error (1).xlsx

**sandy666** · 03-05-2017, 04:31 AM

try
for numbers: =IFERROR(SMALL(IF($B2:$E2>0,$B2:$E2),COLUMNS($B$1:B1)),"")
for headers: =IFERROR(INDEX($B$1:$E$1,,MATCH(SMALL(IF($B2:$E2>0,$B2:$E2),COLUMNS($B$1:B1)),$B2:$E2,0)),"")

both confirm by Control+Shift+Enter then drag down and to the right

with your case it will give you 1st, 2nd, 3rd and 4th smalles value (or headers)

edit:
I didn't read details
You want "remove" duplicates and with, eg. 1,1,2,3, second smallest should be 2?

**JoeyscLee** · 03-05-2017, 05:07 AM

Hello, Sandy
Thanks for reviewing!! Actually, I need to keep the duplicated value and locate all related Headers. For instance of the style D111 (Cell A7), the lowest value is 1 and there are 3 different headers. Hence, hope that the formula can locate them all as well.

Just wondering it may need the helper column at the end if the Headers of lowest value is more than 1.
So, Maybe using the formula " COUNTIF(B7:E7, G7) " to locate " 3 " for the occurrence. But, I don't want to use eyeball to find those Headers.

**JohnTopley** · 03-05-2017, 05:45 AM

Try ..

in M2

=IF(B2=0,"",COUNTIF($B2:$E2,">"&B2)+COUNTIF($B2:B2,B2))

Copy across to P and then down

in G2

=IFERROR(INDEX($B2:$E2,MATCH(LARGE($M2:$P2,1),$M2:$P2,0)),"")

in H2

=IFERROR(INDEX($B$1:$E$1,MATCH(LARGE($M2:$P2,1),$M2:$P2,0)),"")

Copy both down

Copy both pairs to I & J, K & L change highlighted 1 to 2 and 3 respectively.