# Smallest (1st 2nd & 3rd ) Value in range including zero's

#### Basavaraj K H

##### Member
Hi Team
Please find attachment for your reference, in that I need formula 1st 2nd & 3rd Smallest value in range including zero's,

I tried by below formula
=IFERROR(INDEX(\$A\$2:\$J\$2,IF(COUNTIFS(A3:J3,0)<=0,0,MATCH(SMALL(A3:J3,1),A3:J3,0))),"")
it's working for 1st only, I need 2nd & 3rd Smallest value in particular range including zero's

K H Basavaraj

#### Attachments

• 11.1 KB Views: 5

hello

#### Attachments

• 11.9 KB Views: 3

#### Basavaraj K H

##### Member
Sorry, I'm not getting, you just copied from my formula, pls check & do the needful.

#### GraH - Guido

##### Well-Known Member
Perhaps like so?
=IF(COLUMNS(\$K:K)>COUNTIF(\$A3:\$J3,0),0,INDEX(\$A\$2:\$J\$2,AGGREGATE(15,6,(\$A\$1:\$J\$1)/COUNTIF(\$K\$1,\$A3:\$J3),COLUMNS(\$K:K))))

#### Attachments

• 12.8 KB Views: 3

#### p45cal

##### Well-Known Member
You seem to be on Excel 2013 so I can't use TEXTJOIN so in the First zero column in row 3:
=IFERROR(INDEX(\$A\$2:\$J\$2,FIND("¬",SUBSTITUTE(\$A3&\$B3&\$C3&\$D3&\$E3&\$F3&\$G3&\$H3&\$I3&\$J3,"0","¬",1))),"")
in the Second zero column, the same formula but change the red 1 to a 2
in the Third zero column, the same formula but change the red 1 to a 3
Then you can copy the 3 cells down.

Provisos:
No blank cells among the data.
No cells with more than one character in.

Stick with GraH - Guido's

COUNTIF(\$K\$1,\$A3:\$J3)
love it!

Last edited:

#### Peter Bartholomew

##### Well-Known Member
My first formula is a minor variation on Guido's that gives the row of up to 3 values as an array (my ideal it to return all the results with one array formula but that is not straightforward for this problem).
= IF(
{1,2,3}<=COUNTIF(data.rows, 0 ),
INDEX( header, SMALL( IF( data.rows=0, idx ), {1,2,3} ) ),
"" )

Something that has just become possible with Office 365 insider is to reduce the nesting levels by using LET
= LET(
n, COUNTIFS(data.rows, 0 ),
match.idx, IF( data.rows=0, idx ),
selected.idx, SMALL( match.idx, {1,2,3} ),
IF( {1,2,3}<=n, return.hdr, "" )
)

#### Attachments

• 22.2 KB Views: 1