• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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



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:
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.

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

Stick with GraH - Guido's

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



Try this in K3 (with Ctrl + Shift + Enter) and drag across and down