IF function (more than 7)

[Billionzz]

I’m trying to do the following IF function but as far I can tell IF only works with 7 different selections.

If some one could help me with the following I would appreciate it.

Here is what I am trying to do:

Cell B2 contains the numbers I through 8
Cell D2 contains a pre-calculated number (e.g. 53.012)
Cell F2 is the cell that I need an IF function or something similar – the function would be as follows
=IF(B2=1,D2*1.000)
=IF(B2=2,D2*1.00057)
=IF(B2=3,D2*1.00171)
=IF(B2=4,D2*1.002281)
=IF(B2=5,D2*1.003421)
=IF(B2=6,D2*1.003991)
=IF(B2=7,D2*1.004215)
=IF(B2=8,D2*1.004538)

Thanks for your help
Bill

 

[Marcelo Branco]

How can i create a formula in computing tax with this following conditions?


Rate Single Married
5% 5,000 10,000
10% 6,750 13,500
15% 9,250 17,550
20% 12,300 22,000
25% 16,600 26,300
30% 22,450 30,500
35% 31,000 33,300


The employees are classified either Single or Married, then after classifying their status, Tax rate will be identified on which bracket its Salary is...

For example,
Status - single
Salary - 15,000
So, here his/her bracket would fall @ 20%..but can anyone help in with what formula to use.
Thanks in Advance!

Hi burjerKING,

Assuming your table in A1:C8

Try this
<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #c5d9f1; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2472106 class=xl65 height=20 width=64>E</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>F</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>G</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=xl66 height=20>Status</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=xl66>Salary</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=xl66>Rate</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=xl67 height=20>Single</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=xl67 align=right>3000</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=xl68 align=right>0%</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=xl67 height=20>Married</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=xl67 align=right>14000</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=xl68 align=right>10%</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=xl67 height=20>Single</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=xl67 align=right>15000</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=xl68 align=right>20%</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=xl67 height=20>Married</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=xl67 align=right>4000</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=xl68 align=right>0%</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=xl67 height=20>Single</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=xl67 align=right>32000</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=xl68 align=right>35%</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=xl67 height=20>Married</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=xl67 align=right>35000</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=xl68 align=right>35%</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=xl67 height=20>Married</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=xl67 align=right>22000</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=xl68 align=right>20%</TD></TR></TBODY></TABLE>

Formula in G2
=IFERROR(INDEX($A$2:$A$8,MATCH(F2,INDIRECT(CHOOSE(IF(E2="Single",1,2),"B2:B8","C2:C8")))),0)

Copy down if you need

Format column G: percent, 0 decimals

HTH

M.

 

[Aladin Akyurek]

How can i create a formula in computing tax with this following conditions?


Rate Single Married
5% 5,000 10,000
10% 6,750 13,500
15% 9,250 17,550
20% 12,300 22,000
25% 16,600 26,300
30% 22,450 30,500
35% 31,000 33,300


The employees are classified either Single or Married, then after classifying their status, Tax rate will be identified on which bracket its Salary is...

For example,
Status - single
Salary - 15,000
So, here his/her bracket would fall @ 20%..but can anyone help in with what formula to use.
Thanks in Advance!

<TABLE style="WIDTH: 336pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=448><COLGROUP><COL style="WIDTH: 48pt" span=7 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>Rate</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Single</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Married</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Status</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Salary</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Rate</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19 align=right>5%</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=xl66 align=right>5,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=xl66 align=right>10,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=xl65> </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=xl65>single</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=xl66 align=right>15,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=xl65 align=right>0.2</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19 align=right>10%</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=xl66 align=right>6,750</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=xl66 align=right>13,500</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=xl65> </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=xl65> </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=xl65> </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=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19 align=right>15%</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=xl66 align=right>9,250</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=xl66 align=right>17,550</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=xl65> </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=xl65> </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=xl65> </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=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19 align=right>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=xl66 align=right>12,300</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=xl66 align=right>22,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=xl65> </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=xl65> </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=xl65> </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=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19 align=right>25%</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=xl66 align=right>16,600</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=xl66 align=right>26,300</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=xl65> </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=xl65> </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=xl65> </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=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19 align=right>30%</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=xl66 align=right>22,450</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=xl66 align=right>30,500</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=xl65> </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=xl65> </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=xl65> </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=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=19 align=right>35%</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=xl66 align=right>31,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=xl66 align=right>33,300</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=xl65> </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=xl65> </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=xl65> </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=xl65> </TD></TR></TBODY></TABLE>

A1:C8 houses the table, E2:F2 the lookup values.

G2:

=INDEX($A$2:$A$8,MATCH(F2,INDEX($B$2:$C$8,0,MATCH(E2,$B$1:$C$1,0)),1))

 

[Marcelo Branco]

oopss...

No need of CHOOSE function in my formula . I've complicated..

G2=IFERROR(INDEX($A$2:$A$8,MATCH(F2,INDIRECT(IF(E2="Single","B2:B8","C2:C8")))),0)

Probably Aladin formula is better. As usual...

M.

 

[burjerKING]

<table style="width: 336pt; border-collapse: collapse;" width="448" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width: 48pt;" width="64" span="7"></colgroup><tbody><tr style="height: 14.4pt;" height="19"><td style="border: 0.5pt solid windowtext; background-color: transparent; width: 48pt; height: 14.4pt;" class="xl65" width="64" height="19">Rate</td><td style="border-style: solid solid solid none; border-color: windowtext; border-width: 0.5pt 0.5pt 0.5pt medium; background-color: transparent; width: 48pt;" class="xl65" width="64">Single</td><td style="border-style: solid solid solid none; border-color: windowtext; border-width: 0.5pt 0.5pt 0.5pt medium; background-color: transparent; width: 48pt;" class="xl65" width="64">Married</td><td style="border-style: solid solid solid none; border-color: windowtext; border-width: 0.5pt 0.5pt 0.5pt medium; background-color: transparent; width: 48pt;" class="xl65" width="64">
</td><td style="border-style: solid solid solid none; border-color: windowtext; border-width: 0.5pt 0.5pt 0.5pt medium; background-color: transparent; width: 48pt;" class="xl65" width="64">Status</td><td style="border-style: solid solid solid none; border-color: windowtext; border-width: 0.5pt 0.5pt 0.5pt medium; background-color: transparent; width: 48pt;" class="xl65" width="64">Salary</td><td style="border-style: solid solid solid none; border-color: windowtext; border-width: 0.5pt 0.5pt 0.5pt medium; background-color: transparent; width: 48pt;" class="xl65" width="64">Rate</td></tr><tr style="height: 14.4pt;" height="19"><td style="border-style: none solid solid; border-color: windowtext; border-width: medium 0.5pt 0.5pt; background-color: transparent; height: 14.4pt;" class="xl67" align="right" height="19">5%</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl66" align="right">5,000</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl66" align="right">10,000</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl65">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl65">single</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl66" align="right">15,000</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl65" align="right">0.2</td></tr><tr style="height: 14.4pt;" height="19"><td style="border-style: none solid solid; border-color: windowtext; border-width: medium 0.5pt 0.5pt; background-color: transparent; height: 14.4pt;" class="xl67" align="right" height="19">10%</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl66" align="right">6,750</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl66" align="right">13,500</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl65">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl65">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl65">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl65">
</td></tr><tr style="height: 14.4pt;" height="19"><td style="border-style: none solid solid; border-color: windowtext; border-width: medium 0.5pt 0.5pt; background-color: transparent; height: 14.4pt;" class="xl67" align="right" height="19">15%</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl66" align="right">9,250</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl66" align="right">17,550</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl65">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl65">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl65">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl65">
</td></tr><tr style="height: 14.4pt;" height="19"><td style="border-style: none solid solid; border-color: windowtext; border-width: medium 0.5pt 0.5pt; background-color: transparent; height: 14.4pt;" class="xl67" align="right" height="19">20%</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl66" align="right">12,300</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl66" align="right">22,000</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl65">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl65">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl65">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl65">
</td></tr><tr style="height: 14.4pt;" height="19"><td style="border-style: none solid solid; border-color: windowtext; border-width: medium 0.5pt 0.5pt; background-color: transparent; height: 14.4pt;" class="xl67" align="right" height="19">25%</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl66" align="right">16,600</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl66" align="right">26,300</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl65">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl65">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl65">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl65">
</td></tr><tr style="height: 14.4pt;" height="19"><td style="border-style: none solid solid; border-color: windowtext; border-width: medium 0.5pt 0.5pt; background-color: transparent; height: 14.4pt;" class="xl67" align="right" height="19">30%</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl66" align="right">22,450</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl66" align="right">30,500</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl65">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl65">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl65">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl65">
</td></tr><tr style="height: 14.4pt;" height="19"><td style="border-style: none solid solid; border-color: windowtext; border-width: medium 0.5pt 0.5pt; background-color: transparent; height: 14.4pt;" class="xl67" align="right" height="19">35%</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl66" align="right">31,000</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl66" align="right">33,300</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl65">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl65">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl65">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl65">
</td></tr></tbody></table>

A1:C8 houses the table, E2:F2 the lookup values.

G2:

=INDEX($A$2:$A$8,MATCH(F2,INDEX($B$2:$C$8,0,MATCH(E2,$B$1:$C$1,0)),1))

Thanks sir! I'll try this one.

By any how, do you also know how to do it in SQL?