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

[Basavaraj K H]

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

Thank you in advance
K H Basavaraj

 

[herofox]

hello

 

[Basavaraj K H]

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

 

[GraH - Guido]

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

 

[p45cal]

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!

 

[GraH - Guido]

Thank you Pascal

 

[Peter Bartholomew]

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} ),
return.hdr, INDEX( header, selected.idx ),
IF( {1,2,3}<=n, return.hdr, "" )
)

 

[Juniad]

Try this in K3 (with Ctrl + Shift + Enter) and drag across and down
=IFERROR(INDEX($A$2:$J$2,,SMALL(IF($A3:$J3=0,COLUMN($A3:$J3)),COLUMNS($K$3:K3))),"")