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

T

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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
TonyHP8 said:
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
Click to expand...
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)))
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
D
  • Question
How to output a dynamic range where the height of the range varies
Replies
0
Views
969
dagda13
D
J
  • Question
Table values to single row for each value
Replies
5
Views
688
Fluff
Fluff
V
  • Question
Automatic conversion of the currency
Replies
1
Views
691
jmacleary
jmacleary
B
  • Question
Appending a Value to Each Element of a Range Array
Replies
7
Views
2K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,642
Messages
6,120,700
Members
448,979
Latest member
DET4492

Share this page

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
Back
Top