Listing numbers conditionally

[Usercode]

I need a formula to separate and list desired numbers in a certain range from the data i have.

For example, if my data is 4,7,15,19,35,41,8,51,56,64,65,71,75,76,42,38 in A2:P2, then if I want to separate and list the numbers between 50 and 59, the desired outcome should be 51,56 in U2:V2. I attached an example file. It would be good to have the numbers in ascending order. Thanks in advance.

Example file: https://1drv.ms/x/s!AoGkZUHlKui9gR44xEi-kQBlTFs1

 

[Usercode]

Thanks Istvan. This works!!

 

[Special-K99]

I am sure it works, but I couldn't run it. I don't know why. I have Ms Excel 2007.

Thanks for the (rather too late) information.
It will only work on versions of Excel after 2010.

 

[Ingolf]

This works if you can use AGGREGATE

=IFERROR(INDEX($A2:$P$2,1,AGGREGATE(15,6,COLUMN($A2:P2)/(($A2:$P2>=50)*($A2:$P2<=59)),COLUMN(A2))),"")
Array formula, use Ctrl-Shift-Enter
copy across the columns from U2 onwards

Hi,

No, it's not necessary Ctrl-Shift-Enter

 

[Usercode]

Thanks Ingolf. I tried it without Ctrl-Shift-Enter too. but still doesn't work for me. Excel 2007. if its not a lot to ask, can you share a example file where it works?

 

[Special-K99]

Updated formula for Excel 2007

in AO2
=IFERROR(INDEX($A$2:$P$2,1,SMALL(IF(($A$2:$P$2>=70)*($A$2:$P$2<=79),COLUMN($A2:$P$2),9.9E+100),COLUMN(A2))),"")
Array formula, use Ctrl-Shift-Enter
copy across the columns

Amend the lower/upper limits of 70 and 79 for other ranges.

 

[Usercode]

Thanks a lot, Special-K99. it works on my excel now. This formula better suits my task! thanks again.

 

[Ingolf]

Thanks Ingolf. I tried it without Ctrl-Shift-Enter too. but still doesn't work for me. Excel 2007. if its not a lot to ask, can you share a example file where it works?

In Excel 2007 does not exist function AGGREGATE.

I use Excel 2016 and office 365 and that function exist.