Return the 2 lowest numbers within a range

[mpenkala]

Hey,
How can search a range of numbers (ex. E15:I15) and have it find the 2
lowest numbers in the range (most likely 0 and 0) and give me the header
number at the top of the column. And if there's more than 2, give me all of
the lowest possible numbers.

Ex.
Headers - 1 2 3 4 8
1 0 1 2 0

Then I would want the formula to give me the header of that row which the
two 0's are in (so it would give me "2,8".

Thanks!
Matt

 

[ryguy7272]

=SMALL(A1:A10,{2})
=MIN(A1:A10)

Does that help?

 

[T. Valko]

I guess you want the result(s) in a single cell?

Download and install the free Morefunc.xll add-in from:

http://xcell05.free.fr/morefunc/english/index.htm

Then use this array formula**:

=SUBSTITUTE(TRIM(MCONCAT(IF(E16:I16=MIN(E16:I16),E15:I15,"")&" "))," ",",")

Note that this is limited to a max returned string length of 255 characters
(including the delimiter).

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[mpenkala]

Hey T(Biff), this is working great, thanks. But is it possible to use the
formula you gave below and get the column header instead of the 0's? The
formula works great but returns all the lowest numbers (0,0), but I'm hoping
it will return the heard of that column instead. Possible?

Thanks again!
Matt

 

[T. Valko]

It does return the column header. In the formula E15:I15 are the column
headers and E16:I16 are the numbers to match the min.

After reading your post again I think I may have it backwards. So, let's
assume E1:I1 are the column headers and E15:I15 are the numbers (array
entered):

=SUBSTITUTE(TRIM(MCONCAT(IF(E15:I15=MIN(E15:I15),E1:I1,"")&" "))," ",",")

 

[mpenkala]

Excellent work once again! Works like a charm.
Appriciate the help (as always!)

Matt

 

[T. Valko]

You're welcome. Thanks for the feedback!