IF function (more than 7)

B

Billionzz

Board Regular
Joined
Jun 18, 2002
Messages
99
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
 
Sort by date Sort by votes
burjerKING said:
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!
Click to expand...

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.
 
Last edited:
Upvote 0

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
burjerKING said:
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!
Click to expand...

<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))
 
Upvote 0
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... :biggrin:

M.
 
Upvote 0
Aladin Akyurek said:
<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))
Click to expand...

Thanks sir! I'll try this one. :)

By any how, do you also know how to do it in SQL?
 
Upvote 0
You must log in or register to reply here.

Similar threads

Nlhicks
  • Solved
Need Conditional Formatting Formula to Change the Highlight to Red if today is more than 5 years past sticker date and yellow if it is 4 years past
2
Replies
10
Views
235
etaf
etaf
B
  • Question Question
Bin Packing Problem - but not quite?
Replies
5
Views
140
Logit
Logit
M
  • Question Question
Comparing (comma seperated) string values between two columns
2
Replies
14
Views
421
FormR
FormR
J
  • Question Question
Send bulk email attachment error
Replies
3
Views
164
JOHATUP
J
C
  • Question Question
ComboBox and TextBox
Replies
0
Views
352
Cr4ZyPP
C

Forum statistics

Threads
1,225,481
Messages
6,185,246
Members
453,283
Latest member
Shortm88

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