Comparing 2 ranges of different size and dimensions

[Ride The Lightning]

Hi

I have 2 ranges. Range 1 has fixed values and contains the following information:

A B C D
100 0 1.86 1.12
100 0 1.86 1.12
100 0 1.86 1.12
100 0 1.86 1.12
100 0 1.86 1.12

Range 2 is in flux and contains:
E F
25 1.1
30 1.5
15 1.5
200 1.5
200 1.5
15 1.5

What I would am doing is checking if the data in cell E3 (25) in Range 2 fits the criteria in cells A2 (100) and B2 (0) in Range 1. So I have the formula =IF(E3<=$A$2,IF(E3>=$B$2,1,0),0). The following line is IF(E4<=$A$3,IF(E4>=$B$3,1,0),0). Etc. The problem is when I get to cell E7 (15) I want to loop back to start comparing E7 to cells A2 and B2. Of course I can use a variant of the previous formula, but I am using a large amount of data stretching across many columns and want avoid having to enter in alot of formulas manually.

If anyone could suggest an alternative I would be much obliged.

Regards

RTL

 

[AlphaFrog]

<br /><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>A</th><th>B</th><th>K</th><th>N</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">56</td><td style="text-align: right;;">100</td><td style="text-align: right;;">0</td><td style="text-align: right;;">25</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">57</td><td style="text-align: right;;">100</td><td style="text-align: right;;">0</td><td style="text-align: right;;">30</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">58</td><td style="text-align: right;;">100</td><td style="text-align: right;;">0</td><td style="text-align: right;;">15</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">59</td><td style="text-align: right;;">100</td><td style="text-align: right;;">0</td><td style="text-align: right;;">200</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">60</td><td style="text-align: right;;">100</td><td style="text-align: right;;">0</td><td style="text-align: right;;">200</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">61</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">15</td><td style="text-align: right;;">1</td></tr></tbody></table><br /><br /><table 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">N56</th><td style="text-align:left">=(<font color="Blue">K56<=INDEX(<font color="Red">$A$56:$A$60,ROW(<font color="Green">61:61</font>)-INT(<font color="Green">ROW(<font color="Purple">60:60</font>)/5</font>)*5</font>)</font>)*(<font color="Blue">K56>=INDEX(<font color="Red">$B$56:$B$60,ROW(<font color="Green">61:61</font>)-INT(<font color="Green">ROW(<font color="Purple">60:60</font>)/5</font>)*5</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Ride The Lightning]

Alpha Frog,

Can you explain what the ROW function is doing in this instance please? I understand that it is part of the INDEX's function's arguments, but should it start at ROW(56:56) rather than at 61?

Thanks

 

[AlphaFrog]

Put this in any empty column in row 56 and drag it down 15 cells or so.

=ROW(61:61)-INT(ROW(60:60)/5)*5

You'll see that it cycles though the values 1 to 5. The 1-5 value is used as the index of A56:A60.

So the ROW part of the formula is a method to cycle the index value as the formula is dragged down.

 

[Ride The Lightning]

Thank you.