Extract all values (from list) that lie within a range

[palaeontology]

Is there a way to extract all numbers from a list that lie within a range ?

For example, I have a list of ascending numbers in the range B3:N3 and two numbers in P3 and R3 .. the number in P3 is smaller than the one in R3.

I'd like to return all numbers (there's likely to be multiple of them) from the list that lie between the values in P3 and R3 ... the returned numbers could possibly be placed in T3, U3, V3, W3, etc etc etc.

I've tried nth match, and lookup etc etc but can't get my head around it.

 

[Fluff]

How about
=IFERROR(INDEX($B3:$N3,AGGREGATE(15,6,(COLUMN($B3:$N3)-COLUMN($B3)+1)/(($B3:$N3>$P3)*($B3:$N3<$R3)),COLUMNS($A$1:A$1))),"")

 

[palaeontology]

Thankyou, so much, that worked brilliantly.

Now, all I need to do is dissect your formula to see why it works.

Again, thankyou, so much.

Kind regards,

Chris

 

[Fluff]

You're welcome & thanks for the feedback

 

[DanteAmor]

If you want to dissect another formula, I show you an array formula.

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</td><td >Q</td><td >R</td><td >S</td><td >T</td><td >U</td><td >V</td><td >W</td><td >X</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td style="text-align:right; ">5</td><td style="text-align:right; ">17</td><td style="text-align:right; ">29</td><td style="text-align:right; ">41</td><td style="text-align:right; ">53</td><td style="text-align:right; ">65</td><td style="text-align:right; ">77</td><td style="text-align:right; ">89</td><td style="text-align:right; ">101</td><td style="text-align:right; ">113</td><td style="text-align:right; ">125</td><td style="text-align:right; ">137</td><td style="text-align:right; ">149</td><td > </td><td style="text-align:right; ">60</td><td > </td><td style="text-align:right; ">110</td><td > </td><td style="text-align:right; ">65</td><td style="text-align:right; ">77</td><td style="text-align:right; ">89</td><td style="text-align:right; ">101</td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >T3</td><td >{=IFERROR(SMALL(IF(($B$3:$N$3>=$P$3)*($B$3:$N$3<=$R$3),$B$3:$N$3),COLUMNS($T$2:T2)),"")}</td></tr></table></td></tr></table>

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself