Assume you want the results starting cell in C53:

Array entered** :

=IF(COLUMNS($C53:C53)<=COUNT($C50:$J50),INDEX($C50 :$J50,SMALL(IF(ISNUMBER($C50:$J50),COLUMN($C50:$J5 0)-MIN(COLUMN($C50:$J50))+1),COLUMNS($C53:C53))),"")

Copied across.

If the values are in ascending order as is depicted in your sample data then
you can use a less complicated array formula** :

=IF(COLUMNS($C53:C53)<=COUNT($C50:$J50),SMALL(IF(I SNUMBER($C50:$J50),$C50:$J50),COLUMNS($C53:C53))," ")

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

--
Biff
Microsoft Excel MVP


"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:7f71ec39d8399@uwe...
Hi Biff,

Thank you for reply. Your formula representation looks suitable. Please
Post.

T. Valko wrote:
When you say:

no blanks, no empty text
Expected Results:
104, 150, 179

Should have said, "no blanks, no empty text, between the returned numeric
values".

Using a formula the results would be:

| 104 | 150 | 179 | "" | "" | "" | "" | "" |

Where "" is a blank cell. The cell will contain the formula but return a
blank.

That looks fine, if no blanks are actually between the returned numeric
values.

If that's unacceptable then you'll need a macro to do this and you should
post in the programming ng.

Your formula representation is suitable.

Like I said before, Sam's posts are *always* the most complicated posts,
bar
none! <g

<bg


Cheers,
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200802/1