SMALL & LARGE function by code.

[asyamonique]

<TABLE style="WIDTH: 126pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=167 x:str><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2503" width=70><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3441" width=97><TBODY><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 53pt; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=18 width=70></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 73pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=97>(D5:D24)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>148.000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>20.000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 x:num>-30.000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 x:num="-340.65499999999997">-340.655</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>201.750</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 x:num>-661.630</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 x:num>-332.900</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>74.450</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 x:num>-486.350</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>263.980</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>40.000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>166.800</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 x:num>-56.000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>250.000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 x:num>-90.000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>60.000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>200.000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>50.000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>70.000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 x:num>-100.000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl28></TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=18></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18>(C31:C40)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>(D31:D40)</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18>negative</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>positive</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=20 x:num>-661.630</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>263.980</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=20 x:num>-486.350</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>250.000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=20 x:num="-340.65499999999997">-340.655</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>200.000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=20 x:num>-332.900</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>166.800</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=20 x:num>-100.000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>148.000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=20 x:num>-90.000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>74.450</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=20 x:num>-56.000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>70.000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=20 x:num>-30.000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>60.000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=20> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>50.000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=20> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 x:num>40.000</TD></TR></TBODY></TABLE>

Good Day,
Is it possibe to use a code simply to get first 10 positive & negative values from (D5:D24) and place them into the cells as given tables above (C31:C40) & (D31:D40) ?
Many Thanks.

 

[MickG]

Try this:-

Dim Rng As Range
Dim m As Integer
Dim n As Integer
Set Rng = Range(Range("d5"), Range("d" & rows.Count).End(xlUp))
ReDim ray(1 To Rng.Count, 1 To 2)
For n = 1 To 10
    m = m + 1
    If Application.Small(Rng, n) < 0 Then ray(m, 1) = Application.Small(Rng, n)
    If Application.Large(Rng, n) > 0 Then ray(m, 2) = Application.Large(Rng, n)
Next n
Range("C31").Resize(10, 2) = ray

 

[njimack]

Excel Workbook
	C	D
31	-661.63	263.98
32	-486.35	250
33	-340.655	201.75
34	-332.9	200
35	-100	166.8
36	-90	148
37	-56	74.45
38	-30	70
39		60
40		50
Sheet1

 

[asyamonique]

Thanks for the both quick help.
Cheers

 

[Haseeb Avarakkan]

Hello, An Array Formula is not required for this, try this,

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">30</td><td style=";">Negative</td><td style=";">Positive</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;;">8</td><td style="text-align: right;;">12</td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: right;;">-661.63</td><td style="text-align: right;;">263.98</td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="text-align: right;;">-486.35</td><td style="text-align: right;;">250</td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style="text-align: right;;">-340.655</td><td style="text-align: right;;">201.75</td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style="text-align: right;;">-332.9</td><td style="text-align: right;;">200</td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style="text-align: right;;">-100</td><td style="text-align: right;;">166.8</td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style="text-align: right;;">-90</td><td style="text-align: right;;">148</td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style="text-align: right;;">-56</td><td style="text-align: right;;">74.45</td></tr><tr ><td style="color: #161120;text-align: center;">40</td><td style="text-align: right;;">-30</td><td style="text-align: right;;">70</td></tr><tr ><td style="color: #161120;text-align: center;">41</td><td style=";"></td><td style="text-align: right;;">60</td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style=";"></td><td style="text-align: right;;">50</td></tr><tr ><td style="color: #161120;text-align: center;">43</td><td style=";"></td><td style="text-align: right;;">40</td></tr><tr ><td style="color: #161120;text-align: center;">44</td><td style=";"></td><td style="text-align: right;;">20</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C31</th><td style="text-align:left">=COUNTIF(<font color="Blue">D5:D24,"<0"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D31</th><td style="text-align:left">=COUNTIF(<font color="Blue">D5:D24,">0"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C33</th><td style="text-align:left">=IF(<font color="Blue">ROWS(<font color="Red">C$33:C33</font>)<=C$31,SMALL(<font color="Red">$D$5:$D$24,ROWS(<font color="Green">C$33:C33</font>)</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D33</th><td style="text-align:left">=IF(<font color="Blue">ROWS(<font color="Red">D$33:D33</font>)<=D$31,LARGE(<font color="Red">$D$5:$D$24,ROWS(<font color="Green">D$33:D33</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

 

[asyamonique]

Try this:-

Dim Rng As Range
Dim m As Integer
Dim n As Integer
Set Rng = Range(Range("d5"), Range("d" & rows.Count).End(xlUp))
ReDim ray(1 To Rng.Count, 1 To 2)
For n = 1 To 10
    m = m + 1
    If Application.Small(Rng, n) < 0 Then ray(m, 1) = Application.Small(Rng, n)
    If Application.Large(Rng, n) > 0 Then ray(m, 2) = Application.Large(Rng, n)
Next n
Range("C31").Resize(10, 2) = ray

Hi Mick,
How about that sample below? Can we alter the code if we put lookup function for the enique values placed on ranges (A5:A24)?
Many Thanks



<TABLE style="WIDTH: 111pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=149 x:str><COLGROUP><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2616" width=74><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2673" width=75><TBODY><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 55pt; HEIGHT: 13.2pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 height=18 width=74>("A5:A24")</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 width=75>("C5:C24")</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18>A1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 56pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=75 x:num>148</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18>A2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 56pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=75 x:num>20</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18>A3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 56pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 width=75 x:num>-30</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18>A4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 56pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 width=75 x:num="-340.65499999999997">-340.655</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18>A5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 56pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=75 x:num>201.75</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18>A6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 56pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 width=75 x:num>-661.63</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18>A7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 56pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 width=75 x:num>-332.9</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18>A8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 56pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=75 x:num>74.45</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18>A9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 56pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 width=75 x:num>-486.35</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18>A10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 56pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=75 x:num>263.98</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18>A11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 56pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=75 x:num>40</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18>A12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 56pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=75 x:num>166.8</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18>A13</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 56pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 width=75 x:num>-56</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18>A14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 56pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=75 x:num>250</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18>A15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 56pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 width=75 x:num>-90</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18>A16</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 56pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=75 x:num>60</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18>A17</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 56pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=75 x:num>200</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18>A18</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 56pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=75 x:num>50</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18>A19</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 56pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 width=75 x:num>70</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=18>A20</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 56pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 width=75 x:num>-100</TD></TR></TBODY></TABLE>


The below table ranges will be ("B32:E42")

<TABLE style="WIDTH: 296pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=392 x:str><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3498" span=4 width=98><TBODY><TR style="HEIGHT: 13.2pt; mso-height-source: userset" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff; WIDTH: 74pt; HEIGHT: 13.2pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=18 width=98>A6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; COLOR: purple; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0; mso-ignore: style" class=xl65 width=98 x:num>-661.63</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff; WIDTH: 74pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=98>A10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; COLOR: green; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style" class=xl66 width=98 x:num>263.98</TD></TR><TR style="HEIGHT: 13.2pt; mso-height-source: userset" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff; HEIGHT: 13.2pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=18>A9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99; COLOR: purple; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0; mso-ignore: style" class=xl65 width=98 x:num>-486.35</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>A14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99; COLOR: green; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style" class=xl66 width=98 x:num>250</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>A4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99; COLOR: purple; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0; mso-ignore: style" class=xl65 width=98 x:num="-340.65499999999997">-340.655</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>A5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99; COLOR: green; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style" class=xl66 width=98 x:num>201.75</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>A7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99; COLOR: purple; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0; mso-ignore: style" class=xl65 width=98 x:num>-332.9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>A17</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99; COLOR: green; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style" class=xl66 width=98 x:num>200</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>A20</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99; COLOR: purple; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0; mso-ignore: style" class=xl65 width=98 x:num>-100</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>A12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99; COLOR: green; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style" class=xl66 width=98 x:num>166.8</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>A15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99; COLOR: purple; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0; mso-ignore: style" class=xl65 width=98 x:num>-90</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>A1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99; COLOR: green; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style" class=xl66 width=98 x:num>148</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>A13</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99; COLOR: purple; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0; mso-ignore: style" class=xl65 width=98 x:num>-56</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>A8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; WIDTH: 74pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=98 x:num>74.45</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>A3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; WIDTH: 74pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl65 width=98 x:num>-30</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>A19</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; WIDTH: 74pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=98 x:num>70</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; WIDTH: 74pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl65 width=98> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>A16</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; WIDTH: 74pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=98 x:num>60</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; WIDTH: 74pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl67 width=98> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>A18</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; WIDTH: 74pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=98 x:num>50</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; WIDTH: 74pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl67 width=98> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; WIDTH: 74pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=98> </TD></TR></TBODY></TABLE>

 

[Haseeb Avarakkan]

Try,

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;;"></td><td style=";">Negtaive</td><td style="text-align: right;;"></td><td style=";">Positive</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;"></td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;">12</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style=";">A6</td><td style="text-align: right;;">-661.63</td><td style=";">A10</td><td style="text-align: right;;">263.98</td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style=";">A9</td><td style="text-align: right;;">-486.35</td><td style=";">A14</td><td style="text-align: right;;">250</td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style=";">A4</td><td style="text-align: right;;">-340.655</td><td style=";">A5</td><td style="text-align: right;;">201.75</td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style=";">A7</td><td style="text-align: right;;">-332.9</td><td style=";">A17</td><td style="text-align: right;;">200</td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style=";">A20</td><td style="text-align: right;;">-100</td><td style=";">A12</td><td style="text-align: right;;">166.8</td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style=";">A15</td><td style="text-align: right;;">-90</td><td style=";">A1</td><td style="text-align: right;;">148</td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style=";">A13</td><td style="text-align: right;;">-56</td><td style=";">A8</td><td style="text-align: right;;">74.45</td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style=";">A3</td><td style="text-align: right;;">-30</td><td style=";">A19</td><td style="text-align: right;;">70</td></tr><tr ><td style="color: #161120;text-align: center;">40</td><td style=";"></td><td style=";"></td><td style=";">A16</td><td style="text-align: right;;">60</td></tr><tr ><td style="color: #161120;text-align: center;">41</td><td style=";"></td><td style=";"></td><td style=";">A18</td><td style="text-align: right;;">50</td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style=";"></td><td style=";"></td><td style=";">A11</td><td style="text-align: right;;">40</td></tr><tr ><td style="color: #161120;text-align: center;">43</td><td style=";"></td><td style="text-align: right;;"></td><td style=";">A2</td><td style="text-align: right;;">20</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C30</th><td style="text-align:left">=COUNTIF(<font color="Blue">$C$5:$C$24,"<0"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E30</th><td style="text-align:left">=COUNTIF(<font color="Blue">$C$5:$C$24,">0"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C32</th><td style="text-align:left">=IF(<font color="Blue">ROWS(<font color="Red">C$32:C32</font>)<=C$30,SMALL(<font color="Red">$C$5:$C$24,ROWS(<font color="Green">C$32:C32</font>)</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E32</th><td style="text-align:left">=IF(<font color="Blue">ROWS(<font color="Red">E$32:E32</font>)<=E$30,LARGE(<font color="Red">$C$5:$C$24,ROWS(<font color="Green">E$32:E32</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B32</th><td style="text-align:left">{=IF(<font color="Blue">C32="","",INDEX(<font color="Red">$A$5:$A$24,SMALL(<font color="Green">IF(<font color="Purple">$C$5:$C$24=C32,ROW(<font color="Teal">$C$5:$C$24</font>)-ROW(<font color="Teal">$C$5</font>)+1</font>),COUNTIF(<font color="Purple">$C$5:$C$24,C$32:C32</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D32</th><td style="text-align:left">{=IF(<font color="Blue">E32="","",INDEX(<font color="Red">$A$5:$A$24,SMALL(<font color="Green">IF(<font color="Purple">$C$5:$C$24=E32,ROW(<font color="Teal">$C$5:$C$24</font>)-ROW(<font color="Teal">$C$5</font>)+1</font>),COUNTIF(<font color="Purple">$C$5:$C$24,E$32:E32</font>)</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
If you don't have duplicate values in C5:C24 replace B32 & D32 formula to

=IF(C32="","",INDEX($A$5:$A$24,MATCH(C32,$C$5:$C$24,0)))

=IF(E32="","",INDEX($A$5:$A$24,MATCH(E32,$C$5:$C$24,0)))

 

[asyamonique]

Hello, An Array Formula is not required for this, try this,

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>C</TH><TH>D</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">30</TD><TD>Negative</TD><TD>Positive</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">31</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">12</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">32</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">33</TD><TD style="TEXT-ALIGN: right">-661.63</TD><TD style="TEXT-ALIGN: right">263.98</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">34</TD><TD style="TEXT-ALIGN: right">-486.35</TD><TD style="TEXT-ALIGN: right">250</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">35</TD><TD style="TEXT-ALIGN: right">-340.655</TD><TD style="TEXT-ALIGN: right">201.75</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">36</TD><TD style="TEXT-ALIGN: right">-332.9</TD><TD style="TEXT-ALIGN: right">200</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">37</TD><TD style="TEXT-ALIGN: right">-100</TD><TD style="TEXT-ALIGN: right">166.8</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">38</TD><TD style="TEXT-ALIGN: right">-90</TD><TD style="TEXT-ALIGN: right">148</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">39</TD><TD style="TEXT-ALIGN: right">-56</TD><TD style="TEXT-ALIGN: right">74.45</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">40</TD><TD style="TEXT-ALIGN: right">-30</TD><TD style="TEXT-ALIGN: right">70</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">41</TD><TD></TD><TD style="TEXT-ALIGN: right">60</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">42</TD><TD></TD><TD style="TEXT-ALIGN: right">50</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">43</TD><TD></TD><TD style="TEXT-ALIGN: right">40</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">44</TD><TD></TD><TD style="TEXT-ALIGN: right">20</TD></TR></TBODY></TABLE>

Sheet2​

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C31</TH><TD style="TEXT-ALIGN: left">=COUNTIF(D5:D24,"<0")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>D31</TH><TD style="TEXT-ALIGN: left">=COUNTIF(D5:D24,">0")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C33</TH><TD style="TEXT-ALIGN: left">=IF(ROWS(C$33:C33)<=C$31,SMALL($D$5:$D$24,ROWS(C$33:C33)),"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>D33</TH><TD style="TEXT-ALIGN: left">=IF(ROWS(D$33:D33)<=D$31,LARGE($D$5:$D$24,ROWS(D$33:D33)),"")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Thanks Haseeb,
Its very usefull,
If i put the below formula on ("B33:B43") it works fine for the lookup function.
I just wondering that how can i prevent the #N/A texts on empty cells?

{=INDEX(A$5:A$24,MATCH(C32,C$5:C$24,0))}

 

[Haseeb Avarakkan]

Try this;

=LOOKUP(REPT("Z",250),CHOOSE({1,2},"",INDEX(A$5:A$24,MATCH(C32,C$5:C$24,0))))

Assume A5:A25 contains text not numbers.

 

[asyamonique]

Try this;

=LOOKUP(REPT("Z",250),CHOOSE({1,2},"",INDEX(A$5:A$24,MATCH(C32,C$5:C$24,0))))

Assume A5:A25 contains text not numbers.

Text & numbers!