Double range data selection(?). Lookup of 1 output value given 2 inputs belonging to defined ranges

TonyHP8

New Member
Joined
Oct 26, 2011
Messages
2
Hello everyone

This is probably not a new issue, but was not able to find anything on it
FYI - I am using Excel 2007

It will be much easier to understand if you look at the example table below (I am not able to post an image, sorry...)
I am given the table with assigned values, plus couples of values (A; B). For each of the couples I filled in the desired output.

See first row: A is given as 14.20%, so in btw 10% and 15%. That would narrow the value search to subset 3. Selection then continues given value of B of 1. For subset 3, a B value of 1 will return 1.25%

How to obtain the desired value? I tried lookups, index, match, etc etc,
One solution might be an endless chain of IF functions, really not ideal...
Any suggestion please? :confused:


<table width="602" border="0" cellpadding="0" cellspacing="0"><col style="width: 36pt;" width="48"> <col style="width: 48pt;" width="64" span="5"> <col style="width: 32pt;" width="42"> <col style="width: 48pt;" width="64" span="3"> <tbody><tr style="height: 13.5pt;" height="18"> <td style="height: 13.5pt; width: 36pt;" width="48" height="18">
</td> <td style="width: 48pt;" width="64">Table</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 32pt;" width="42">
</td> <td style="width: 48pt;" width="64">Examples</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 26.25pt;" height="35"> <td style="height: 26.25pt;" height="35">Subset</td> <td class="xl81" style="width: 48pt;" width="64">A min</td> <td class="xl82" style="border-left: medium none; width: 48pt;" width="64">A max</td> <td class="xl81" style="width: 48pt;" width="64">B min</td> <td class="xl82" style="border-left: medium none; width: 48pt;" width="64">B max</td> <td class="xl68" style="border-left: medium none; width: 48pt;" width="64">Assigned value</td> <td class="xl67">
</td> <td class="xl79">A</td> <td class="xl79" style="border-left: medium none;">B</td> <td class="xl80" style="border-left: medium none; width: 48pt;" width="64">Desired output</td> </tr> <tr style="height: 12.75pt;" height="17"> <td rowspan="4" class="xl89" style="border-bottom: 1pt solid black; height: 51.75pt;" height="69">1</td> <td class="xl86" style="border-top: medium none; width: 48pt;" width="64">0%</td> <td class="xl83" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">5%</td> <td class="xl69" style="border-top: medium none; width: 48pt;" width="64">0</td> <td class="xl70" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">0</td> <td class="xl71" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">0.50%</td> <td class="xl67">
</td> <td class="xl78" style="border-top: medium none;">14.20%</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">1.25%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl87" style="height: 12.75pt; border-top: medium none; width: 48pt;" width="64" height="17">0%</td> <td class="xl84" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">5%</td> <td class="xl72" style="border-top: medium none; width: 48pt;" width="64">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">1</td> <td class="xl73" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">0.25%</td> <td class="xl67">
</td> <td class="xl78" style="border-top: medium none;">19.60%</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">2.25%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl87" style="height: 12.75pt; border-top: medium none; width: 48pt;" width="64" height="17">0%</td> <td class="xl84" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">5%</td> <td class="xl72" style="border-top: medium none; width: 48pt;" width="64">2</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">4</td> <td class="xl73" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">0.00%</td> <td class="xl67">
</td> <td class="xl78" style="border-top: medium none;">15.56%</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">3</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">1.25%</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl88" style="height: 13.5pt; border-top: medium none; width: 48pt;" width="64" height="18">0%</td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">5%</td> <td class="xl74" style="border-top: medium none; width: 48pt;" width="64">5</td> <td class="xl75" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">100</td> <td class="xl76" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">0.00%</td> <td class="xl67">
</td> <td class="xl78" style="border-top: medium none;">14.06%</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">8</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.25%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td rowspan="4" class="xl89" style="border-bottom: 1pt solid black; height: 51.75pt; border-top: medium none;" height="69">2</td> <td class="xl83" style="border-top: medium none; width: 48pt;" width="64">5%</td> <td class="xl83" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">10%</td> <td class="xl69" style="border-top: medium none; width: 48pt;" width="64">0</td> <td class="xl70" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">0</td> <td class="xl71" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">1.25%</td> <td class="xl67">
</td> <td class="xl78" style="border-top: medium none;">11.60%</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">1.75%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl84" style="height: 12.75pt; border-top: medium none; width: 48pt;" width="64" height="17">5%</td> <td class="xl84" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">10%</td> <td class="xl72" style="border-top: medium none; width: 48pt;" width="64">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">1</td> <td class="xl73" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">0.75%</td> <td class="xl67">
</td> <td class="xl78" style="border-top: medium none;">10.00%</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">4</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.25%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl84" style="height: 12.75pt; border-top: medium none; width: 48pt;" width="64" height="17">5%</td> <td class="xl84" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">10%</td> <td class="xl72" style="border-top: medium none; width: 48pt;" width="64">2</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">4</td> <td class="xl73" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">0.25%</td> <td class="xl67">
</td> <td class="xl78" style="border-top: medium none;">10.64%</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">4</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.75%</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl85" style="height: 13.5pt; border-top: medium none; width: 48pt;" width="64" height="18">5%</td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">10%</td> <td class="xl74" style="border-top: medium none; width: 48pt;" width="64">5</td> <td class="xl75" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">100</td> <td class="xl76" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">0.00%</td> <td class="xl67">
</td> <td class="xl78" style="border-top: medium none;">10.30%</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">9</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.25%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td rowspan="4" class="xl89" style="border-bottom: 1pt solid black; height: 51.75pt; border-top: medium none;" height="69">3</td> <td class="xl83" style="border-top: medium none; width: 48pt;" width="64">10%</td> <td class="xl83" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">15%</td> <td class="xl69" style="border-top: medium none; width: 48pt;" width="64">0</td> <td class="xl70" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">0</td> <td class="xl71" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">1.75%</td> <td class="xl67">
</td> <td class="xl78" style="border-top: medium none;">8.36%</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">1.25%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl84" style="height: 12.75pt; border-top: medium none; width: 48pt;" width="64" height="17">10%</td> <td class="xl84" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">15%</td> <td class="xl72" style="border-top: medium none; width: 48pt;" width="64">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">1</td> <td class="xl73" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">1.25%</td> <td class="xl67">
</td> <td class="xl78" style="border-top: medium none;">4.42%</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.50%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl84" style="height: 12.75pt; border-top: medium none; width: 48pt;" width="64" height="17">10%</td> <td class="xl84" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">15%</td> <td class="xl72" style="border-top: medium none; width: 48pt;" width="64">2</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">4</td> <td class="xl73" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">0.75%</td> <td class="xl67">
</td> <td class="xl78" style="border-top: medium none;">11.59%</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">8</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.25%</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl85" style="height: 13.5pt; border-top: medium none; width: 48pt;" width="64" height="18">10%</td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">15%</td> <td class="xl74" style="border-top: medium none; width: 48pt;" width="64">5</td> <td class="xl75" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">100</td> <td class="xl76" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">0.25%</td> <td class="xl67">
</td> <td class="xl78" style="border-top: medium none;">27.96%</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">0</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">2.75%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td rowspan="4" class="xl89" style="border-bottom: 1pt solid black; height: 51.75pt; border-top: medium none;" height="69">4</td> <td class="xl83" style="border-top: medium none; width: 48pt;" width="64">15%</td> <td class="xl83" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">20%</td> <td class="xl69" style="border-top: medium none; width: 48pt;" width="64">0</td> <td class="xl70" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">0</td> <td class="xl71" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">2.25%</td> <td class="xl67">
</td> <td class="xl78" style="border-top: medium none;">9.92%</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">0.25%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl84" style="height: 12.75pt; border-top: medium none; width: 48pt;" width="64" height="17">15%</td> <td class="xl84" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">20%</td> <td class="xl72" style="border-top: medium none; width: 48pt;" width="64">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">1</td> <td class="xl73" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">1.75%</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl84" style="height: 12.75pt; border-top: medium none; width: 48pt;" width="64" height="17">15%</td> <td class="xl84" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">20%</td> <td class="xl72" style="border-top: medium none; width: 48pt;" width="64">2</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">4</td> <td class="xl73" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">1.25%</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">
</td> <td>
</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl85" style="height: 13.5pt; border-top: medium none; width: 48pt;" width="64" height="18">15%</td> <td class="xl85" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">20%</td> <td class="xl74" style="border-top: medium none; width: 48pt;" width="64">5</td> <td class="xl75" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">100</td> <td class="xl76" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">0.75%</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">
</td> <td>
</td> </tr> </tbody></table>

H:%5C
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Hello everyone

This is probably not a new issue, but was not able to find anything on it
FYI - I am using Excel 2007

It will be much easier to understand if you look at the example table below (I am not able to post an image, sorry...)
I am given the table with assigned values, plus couples of values (A; B). For each of the couples I filled in the desired output.

See first row: A is given as 14.20%, so in btw 10% and 15%. That would narrow the value search to subset 3. Selection then continues given value of B of 1. For subset 3, a B value of 1 will return 1.25%

How to obtain the desired value? I tried lookups, index, match, etc etc,
One solution might be an endless chain of IF functions, really not ideal...
Any suggestion please? :confused:


<TABLE border=0 cellSpacing=0 cellPadding=0 width=602><COLGROUP><COL style="WIDTH: 36pt" width=48><COL style="WIDTH: 48pt" span=5 width=64><COL style="WIDTH: 32pt" width=42><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="WIDTH: 36pt; HEIGHT: 13.5pt" height=18 width=48>

</TD><TD style="WIDTH: 48pt" width=64>Table</TD><TD style="WIDTH: 48pt" width=64>

</TD><TD style="WIDTH: 48pt" width=64>

</TD><TD style="WIDTH: 48pt" width=64>

</TD><TD style="WIDTH: 48pt" width=64>

</TD><TD style="WIDTH: 32pt" width=42>

</TD><TD style="WIDTH: 48pt" width=64>Examples</TD><TD style="WIDTH: 48pt" width=64>

</TD><TD style="WIDTH: 48pt" width=64>

</TD></TR><TR style="HEIGHT: 26.25pt" height=35><TD style="HEIGHT: 26.25pt" height=35>Subset</TD><TD style="WIDTH: 48pt" class=xl81 width=64>A min</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt" class=xl82 width=64>A max</TD><TD style="WIDTH: 48pt" class=xl81 width=64>B min</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt" class=xl82 width=64>B max</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt" class=xl68 width=64>Assigned value</TD><TD class=xl67>

</TD><TD class=xl79>A</TD><TD style="BORDER-LEFT: medium none" class=xl79>B</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt" class=xl80 width=64>Desired output</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: black 1pt solid; HEIGHT: 51.75pt" class=xl89 height=69 rowSpan=4>1</TD><TD style="WIDTH: 48pt; BORDER-TOP: medium none" class=xl86 width=64>0%</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl83 width=64>5%</TD><TD style="WIDTH: 48pt; BORDER-TOP: medium none" class=xl69 width=64>0</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl70 width=64>0</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl71 width=64>0.50%</TD><TD class=xl67>

</TD><TD style="BORDER-TOP: medium none" class=xl78>14.20%</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl77>1</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl78>1.25%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: medium none" class=xl87 height=17 width=64>0%</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl84 width=64>5%</TD><TD style="WIDTH: 48pt; BORDER-TOP: medium none" class=xl72 width=64>1</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl66 width=64>1</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl73 width=64>0.25%</TD><TD class=xl67>

</TD><TD style="BORDER-TOP: medium none" class=xl78>19.60%</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl77>0</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl78>2.25%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: medium none" class=xl87 height=17 width=64>0%</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl84 width=64>5%</TD><TD style="WIDTH: 48pt; BORDER-TOP: medium none" class=xl72 width=64>2</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl66 width=64>4</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl73 width=64>0.00%</TD><TD class=xl67>

</TD><TD style="BORDER-TOP: medium none" class=xl78>15.56%</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl77>3</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl78>1.25%</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: medium none" class=xl88 height=18 width=64>0%</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl85 width=64>5%</TD><TD style="WIDTH: 48pt; BORDER-TOP: medium none" class=xl74 width=64>5</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl75 width=64>100</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl76 width=64>0.00%</TD><TD class=xl67>

</TD><TD style="BORDER-TOP: medium none" class=xl78>14.06%</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl77>8</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl78>0.25%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: black 1pt solid; HEIGHT: 51.75pt; BORDER-TOP: medium none" class=xl89 height=69 rowSpan=4>2</TD><TD style="WIDTH: 48pt; BORDER-TOP: medium none" class=xl83 width=64>5%</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl83 width=64>10%</TD><TD style="WIDTH: 48pt; BORDER-TOP: medium none" class=xl69 width=64>0</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl70 width=64>0</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl71 width=64>1.25%</TD><TD class=xl67>

</TD><TD style="BORDER-TOP: medium none" class=xl78>11.60%</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl77>0</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl78>1.75%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: medium none" class=xl84 height=17 width=64>5%</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl84 width=64>10%</TD><TD style="WIDTH: 48pt; BORDER-TOP: medium none" class=xl72 width=64>1</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl66 width=64>1</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl73 width=64>0.75%</TD><TD class=xl67>

</TD><TD style="BORDER-TOP: medium none" class=xl78>10.00%</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl77>4</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl78>0.25%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: medium none" class=xl84 height=17 width=64>5%</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl84 width=64>10%</TD><TD style="WIDTH: 48pt; BORDER-TOP: medium none" class=xl72 width=64>2</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl66 width=64>4</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl73 width=64>0.25%</TD><TD class=xl67>

</TD><TD style="BORDER-TOP: medium none" class=xl78>10.64%</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl77>4</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl78>0.75%</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: medium none" class=xl85 height=18 width=64>5%</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl85 width=64>10%</TD><TD style="WIDTH: 48pt; BORDER-TOP: medium none" class=xl74 width=64>5</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl75 width=64>100</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl76 width=64>0.00%</TD><TD class=xl67>

</TD><TD style="BORDER-TOP: medium none" class=xl78>10.30%</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl77>9</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl78>0.25%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: black 1pt solid; HEIGHT: 51.75pt; BORDER-TOP: medium none" class=xl89 height=69 rowSpan=4>3</TD><TD style="WIDTH: 48pt; BORDER-TOP: medium none" class=xl83 width=64>10%</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl83 width=64>15%</TD><TD style="WIDTH: 48pt; BORDER-TOP: medium none" class=xl69 width=64>0</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl70 width=64>0</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl71 width=64>1.75%</TD><TD class=xl67>

</TD><TD style="BORDER-TOP: medium none" class=xl78>8.36%</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl77>0</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl78>1.25%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: medium none" class=xl84 height=17 width=64>10%</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl84 width=64>15%</TD><TD style="WIDTH: 48pt; BORDER-TOP: medium none" class=xl72 width=64>1</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl66 width=64>1</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl73 width=64>1.25%</TD><TD class=xl67>

</TD><TD style="BORDER-TOP: medium none" class=xl78>4.42%</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl77>0</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl78>0.50%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: medium none" class=xl84 height=17 width=64>10%</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl84 width=64>15%</TD><TD style="WIDTH: 48pt; BORDER-TOP: medium none" class=xl72 width=64>2</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl66 width=64>4</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl73 width=64>0.75%</TD><TD class=xl67>

</TD><TD style="BORDER-TOP: medium none" class=xl78>11.59%</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl77>8</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl78>0.25%</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: medium none" class=xl85 height=18 width=64>10%</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl85 width=64>15%</TD><TD style="WIDTH: 48pt; BORDER-TOP: medium none" class=xl74 width=64>5</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl75 width=64>100</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl76 width=64>0.25%</TD><TD class=xl67>

</TD><TD style="BORDER-TOP: medium none" class=xl78>27.96%</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl77>0</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl78>2.75%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: black 1pt solid; HEIGHT: 51.75pt; BORDER-TOP: medium none" class=xl89 height=69 rowSpan=4>4</TD><TD style="WIDTH: 48pt; BORDER-TOP: medium none" class=xl83 width=64>15%</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl83 width=64>20%</TD><TD style="WIDTH: 48pt; BORDER-TOP: medium none" class=xl69 width=64>0</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl70 width=64>0</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl71 width=64>2.25%</TD><TD class=xl67>

</TD><TD style="BORDER-TOP: medium none" class=xl78>9.92%</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl77>2</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl78>0.25%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: medium none" class=xl84 height=17 width=64>15%</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl84 width=64>20%</TD><TD style="WIDTH: 48pt; BORDER-TOP: medium none" class=xl72 width=64>1</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl66 width=64>1</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl73 width=64>1.75%</TD><TD class=xl67>

</TD><TD class=xl67>

</TD><TD class=xl67>

</TD><TD>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: medium none" class=xl84 height=17 width=64>15%</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl84 width=64>20%</TD><TD style="WIDTH: 48pt; BORDER-TOP: medium none" class=xl72 width=64>2</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl66 width=64>4</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl73 width=64>1.25%</TD><TD class=xl67>

</TD><TD class=xl67>

</TD><TD class=xl67>

</TD><TD>

</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: medium none" class=xl85 height=18 width=64>15%</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl85 width=64>20%</TD><TD style="WIDTH: 48pt; BORDER-TOP: medium none" class=xl74 width=64>5</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl75 width=64>100</TD><TD style="BORDER-LEFT: medium none; WIDTH: 48pt; BORDER-TOP: medium none" class=xl76 width=64>0.75%</TD><TD class=xl67>

</TD><TD class=xl67>

</TD><TD class=xl67>

</TD><TD>

</TD></TR></TBODY></TABLE>

H:%5C
Control+shift+enter, not just enter, and copy down:
Rich (BB code):
=MIN(IF((H3>=$B$3:$B$18)*(H3<=$C$3:$C$18),
  IF((I3>=$D$3:$D$18)*(I3<=$E$3:$E$18),$F$3:$F$18)))
 

jfish1288

Board Regular
Joined
Jun 22, 2011
Messages
116
if you changed the subset column to have the subset given on each line like below


<table border="0" cellpadding="0" cellspacing="0" width="576"><colgroup><col style="width:48pt" span="9" width="64"> </colgroup><tbody><tr style="height:25.5pt" height="34"> <td class="xl22" style="height:25.5pt;width:48pt" height="34" width="64">Subset</td> <td class="xl22" style="width:48pt" width="64">A min</td> <td class="xl22" style="width:48pt" width="64">A max</td> <td class="xl22" style="width:48pt" width="64">B min</td> <td class="xl22" style="width:48pt" width="64">B max</td> <td class="xl22" style="width:48pt" width="64">Assigned value</td> <td class="xl22" style="width:48pt" width="64">
</td> <td class="xl22" style="width:48pt" width="64">A</td> <td class="xl22" style="width:48pt" width="64">B</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl22" style="height:15.0pt; width:48pt" align="right" height="20" width="64">1</td> <td class="xl23" style="width:48pt" align="right" width="64">0%</td> <td class="xl23" style="width:48pt" align="right" width="64">5%</td> <td class="xl22" style="width:48pt" align="right" width="64">0</td> <td class="xl22" style="width:48pt" align="right" width="64">0</td> <td class="xl24" style="width:48pt" align="right" width="64">0.50%</td> <td class="xl22" style="width:48pt" width="64">
</td> <td class="xl24" style="width:48pt" align="right" width="64">14.20%</td> <td class="xl22" style="width:48pt" align="right" width="64">1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl22" style="height:15.0pt; width:48pt" align="right" height="20" width="64">1</td> <td class="xl23" style="width:48pt" align="right" width="64">0%</td> <td class="xl23" style="width:48pt" align="right" width="64">5%</td> <td class="xl22" style="width:48pt" align="right" width="64">1</td> <td class="xl22" style="width:48pt" align="right" width="64">1</td> <td class="xl24" style="width:48pt" align="right" width="64">0.25%</td> <td class="xl22" style="width:48pt" width="64">
</td> <td class="xl24" style="width:48pt" align="right" width="64">19.60%</td> <td class="xl22" style="width:48pt" align="right" width="64">0</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl22" style="height:15.0pt; width:48pt" align="right" height="20" width="64">1</td> <td class="xl23" style="width:48pt" align="right" width="64">0%</td> <td class="xl23" style="width:48pt" align="right" width="64">5%</td> <td class="xl22" style="width:48pt" align="right" width="64">2</td> <td class="xl22" style="width:48pt" align="right" width="64">4</td> <td class="xl24" style="width:48pt" align="right" width="64">0.00%</td> <td class="xl22" style="width:48pt" width="64">
</td> <td class="xl24" style="width:48pt" align="right" width="64">15.56%</td> <td class="xl22" style="width:48pt" align="right" width="64">3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl22" style="height:15.0pt; width:48pt" align="right" height="20" width="64">1</td> <td class="xl23" style="width:48pt" align="right" width="64">0%</td> <td class="xl23" style="width:48pt" align="right" width="64">5%</td> <td class="xl22" style="width:48pt" align="right" width="64">5</td> <td class="xl22" style="width:48pt" align="right" width="64">100</td> <td class="xl24" style="width:48pt" align="right" width="64">0.00%</td> <td class="xl22" style="width:48pt" width="64">
</td> <td class="xl24" style="width:48pt" align="right" width="64">14.06%</td> <td class="xl22" style="width:48pt" align="right" width="64">8</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl22" style="height:15.0pt; width:48pt" align="right" height="20" width="64">2</td> <td class="xl23" style="width:48pt" align="right" width="64">5%</td> <td class="xl23" style="width:48pt" align="right" width="64">10%</td> <td class="xl22" style="width:48pt" align="right" width="64">0</td> <td class="xl22" style="width:48pt" align="right" width="64">0</td> <td class="xl24" style="width:48pt" align="right" width="64">1.25%</td> <td class="xl22" style="width:48pt" width="64">
</td> <td class="xl24" style="width:48pt" align="right" width="64">11.60%</td> <td class="xl22" style="width:48pt" align="right" width="64">0</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl22" style="height:15.0pt; width:48pt" align="right" height="20" width="64">2</td> <td class="xl23" style="width:48pt" align="right" width="64">5%</td> <td class="xl23" style="width:48pt" align="right" width="64">10%</td> <td class="xl22" style="width:48pt" align="right" width="64">1</td> <td class="xl22" style="width:48pt" align="right" width="64">1</td> <td class="xl24" style="width:48pt" align="right" width="64">0.75%</td> <td class="xl22" style="width:48pt" width="64">
</td> <td class="xl24" style="width:48pt" align="right" width="64">10.00%</td> <td class="xl22" style="width:48pt" align="right" width="64">4</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl22" style="height:15.0pt; width:48pt" align="right" height="20" width="64">2</td> <td class="xl23" style="width:48pt" align="right" width="64">5%</td> <td class="xl23" style="width:48pt" align="right" width="64">10%</td> <td class="xl22" style="width:48pt" align="right" width="64">2</td> <td class="xl22" style="width:48pt" align="right" width="64">4</td> <td class="xl24" style="width:48pt" align="right" width="64">0.25%</td> <td class="xl22" style="width:48pt" width="64">
</td> <td class="xl24" style="width:48pt" align="right" width="64">10.64%</td> <td class="xl22" style="width:48pt" align="right" width="64">4</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl22" style="height:15.0pt; width:48pt" align="right" height="20" width="64">2</td> <td class="xl23" style="width:48pt" align="right" width="64">5%</td> <td class="xl23" style="width:48pt" align="right" width="64">10%</td> <td class="xl22" style="width:48pt" align="right" width="64">5</td> <td class="xl22" style="width:48pt" align="right" width="64">100</td> <td class="xl24" style="width:48pt" align="right" width="64">0.00%</td> <td class="xl22" style="width:48pt" width="64">
</td> <td class="xl24" style="width:48pt" align="right" width="64">10.30%</td> <td class="xl22" style="width:48pt" align="right" width="64">9</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl22" style="height:15.0pt; width:48pt" align="right" height="20" width="64">3</td> <td class="xl23" style="width:48pt" align="right" width="64">10%</td> <td class="xl23" style="width:48pt" align="right" width="64">15%</td> <td class="xl22" style="width:48pt" align="right" width="64">0</td> <td class="xl22" style="width:48pt" align="right" width="64">0</td> <td class="xl24" style="width:48pt" align="right" width="64">1.75%</td> <td class="xl22" style="width:48pt" width="64">
</td> <td class="xl24" style="width:48pt" align="right" width="64">8.36%</td> <td class="xl22" style="width:48pt" align="right" width="64">0</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl22" style="height:15.0pt; width:48pt" align="right" height="20" width="64">3</td> <td class="xl23" style="width:48pt" align="right" width="64">10%</td> <td class="xl23" style="width:48pt" align="right" width="64">15%</td> <td class="xl22" style="width:48pt" align="right" width="64">1</td> <td class="xl22" style="width:48pt" align="right" width="64">1</td> <td class="xl24" style="width:48pt" align="right" width="64">1.25%</td> <td class="xl22" style="width:48pt" width="64">
</td> <td class="xl24" style="width:48pt" align="right" width="64">4.42%</td> <td class="xl22" style="width:48pt" align="right" width="64">0</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl22" style="height:15.0pt; width:48pt" align="right" height="20" width="64">3</td> <td class="xl23" style="width:48pt" align="right" width="64">10%</td> <td class="xl23" style="width:48pt" align="right" width="64">15%</td> <td class="xl22" style="width:48pt" align="right" width="64">2</td> <td class="xl22" style="width:48pt" align="right" width="64">4</td> <td class="xl24" style="width:48pt" align="right" width="64">0.75%</td> <td class="xl22" style="width:48pt" width="64">
</td> <td class="xl24" style="width:48pt" align="right" width="64">11.59%</td> <td class="xl22" style="width:48pt" align="right" width="64">8</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl22" style="height:15.0pt; width:48pt" align="right" height="20" width="64">3</td> <td class="xl23" style="width:48pt" align="right" width="64">10%</td> <td class="xl23" style="width:48pt" align="right" width="64">15%</td> <td class="xl22" style="width:48pt" align="right" width="64">5</td> <td class="xl22" style="width:48pt" align="right" width="64">100</td> <td class="xl24" style="width:48pt" align="right" width="64">0.25%</td> <td class="xl22" style="width:48pt" width="64">
</td> <td class="xl24" style="width:48pt" align="right" width="64">27.96%</td> <td class="xl22" style="width:48pt" align="right" width="64">0</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl22" style="height:15.0pt; width:48pt" align="right" height="20" width="64">4</td> <td class="xl23" style="width:48pt" align="right" width="64">15%</td> <td class="xl23" style="width:48pt" align="right" width="64">20%</td> <td class="xl22" style="width:48pt" align="right" width="64">0</td> <td class="xl22" style="width:48pt" align="right" width="64">0</td> <td class="xl24" style="width:48pt" align="right" width="64">2.25%</td> <td class="xl22" style="width:48pt" width="64">
</td> <td class="xl24" style="width:48pt" align="right" width="64">9.92%</td> <td class="xl22" style="width:48pt" align="right" width="64">2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt; width:48pt" align="right" height="17" width="64">4</td> <td class="xl23" style="width:48pt" align="right" width="64">15%</td> <td class="xl23" style="width:48pt" align="right" width="64">20%</td> <td class="xl22" style="width:48pt" align="right" width="64">1</td> <td class="xl22" style="width:48pt" align="right" width="64">1</td> <td class="xl24" style="width:48pt" align="right" width="64">1.75%</td> <td class="xl22" style="width:48pt" width="64">
</td> <td class="xl22" style="width:48pt" width="64">
</td> <td class="xl22" style="width:48pt" width="64">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt; width:48pt" align="right" height="17" width="64">4</td> <td class="xl23" style="width:48pt" align="right" width="64">15%</td> <td class="xl23" style="width:48pt" align="right" width="64">20%</td> <td class="xl22" style="width:48pt" align="right" width="64">2</td> <td class="xl22" style="width:48pt" align="right" width="64">4</td> <td class="xl24" style="width:48pt" align="right" width="64">1.25%</td> <td class="xl22" style="width:48pt" width="64">
</td> <td class="xl22" style="width:48pt" width="64">
</td> <td class="xl22" style="width:48pt" width="64">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt; width:48pt" align="right" height="17" width="64">4</td> <td class="xl23" style="width:48pt" align="right" width="64">15%</td> <td class="xl23" style="width:48pt" align="right" width="64">20%</td> <td class="xl22" style="width:48pt" align="right" width="64">5</td> <td class="xl22" style="width:48pt" align="right" width="64">100</td> <td class="xl24" style="width:48pt" align="right" width="64">0.75%</td> <td class="xl22" style="width:48pt" width="64">
</td> <td class="xl22" style="width:48pt" width="64">
</td> <td class="xl22" style="width:48pt" width="64">
</td> </tr> </tbody></table>

you could use this formula, assuming the table starts with "Subset" being in cell A1 and the input A and B being columns H and I respectively

=INDEX($F$2:$F$17,MATCH(1,IF(INDEX($A$2:$A$17,MATCH(1,IF(H2>=$B$2:$B$17,IF(H2<=$C$2:$C$17,1)),0))=$A$2:$A$17,IF(I2>=$D$2:$D$17,IF(I2<=$E$2:$E$17,1))),0))
confirmed with ctrl+shift+enter
 

Watch MrExcel Video

Forum statistics

Threads
1,123,346
Messages
5,601,087
Members
414,426
Latest member
fraru

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top