If Statements with multiple criteria

[KyleOliver]

Hi,

I am stuck and need some help please.

In my excel workbook I have two worksheets namely "Payroll Register" & "TAX Rates & Rebates"

I am trying to reflect the employee's TAX Rebate based on his/her age as per the following table;

The "Payroll Register" worksheet contains the employee's details such as age and on the 2nd worksheet, "TAX Rates & Rebates" is the following TAX Rebate table;

Tax Rebate​​
​Primary (64yrs and younger) = R13 635
​​Secondary (65yrs and older) = R21 114
​Tertiary (75yrs and older) = R23 607

---

The formula I used is;
=IF($D$4<='TAX Rates & Rebates'!$L$9,'TAX Rates & Rebates'!$N$9,IF($D$4>='TAX Rates & Rebates'!$L$10,'TAX Rates & Rebates'!$O$10,IF($D$4>='TAX Rates & Rebates'!M10,'TAX Rates & Rebates'!$O$11)))

---

cell $D$4 is the employee's age which is reflected in the "Payroll Register" worksheet
cell 'TAX Rates & Rebates'!$L$9 has the value "64" (yrs of age)
cell 'TAX Rates & Rebates'!$N$9 has the value "R13 635" (TAX rebate amount)

cell $D$4 is the employee's age which is reflected in the "Payroll Register" worksheet
cell 'TAX Rates & Rebates'!$L$10 has the value "65" (yrs of age)
cell 'TAX Rates & Rebates'!$N$9 has the value "R21 114" (TAX rebate amount)

cell $D$4 is the employee's age which is reflected in the "Payroll Register" worksheet
cell 'TAX Rates & Rebates'!$L$10 has the value "75" (yrs of age)
cell 'TAX Rates & Rebates'!$N$9 has the value "R23 607" (TAX rebate amount)

This formula is not working and I don't know what else to do

Could someone please assist?

Many thanks,
Kyle

 

[jtakw]

Hi,

Your description of the Tax Table and your data layout Do Not match the cell references in your formula, start by correcting the sheet and/or the formula cell references.
Without the correct information, won't be able to help adjust your formula.

 

[jtakw]

Hi,

Here's sample Table and Data sheet I created as close to your description as I can get, see if this helps, if it doesn't, you need to clarify your layout.

Excel 2010
	C	D	E
3	Person	Age	Tax Rate/Rebate
4	A	63	R13 635
5	B	64	R13 635
6	C	65	R21 114
7	D	74	R21 114
8	E	75	R23 607
9	F	80	R23 607
Payroll Register

Cell Formulas
Range	Formula
E4	=IF($D4<='TAX Rates & Rebates'!$L$9,'TAX Rates & Rebates'!$N$9,IF($D4<'TAX Rates & Rebates'!$L$11,'TAX Rates & Rebates'!$N$10,'TAX Rates & Rebates'!$N$11))

Excel 2010
	L	M	N
8	Tax Rebate??
9	64		R13 635
10	65		R21 114
11	75		R23 607
TAX Rates & Rebates

 

[KyleOliver]

Hi,

Here's sample Table and Data sheet I created as close to your description as I can get, see if this helps, if it doesn't, you need to clarify your layout.

Excel 2010

	C	D	E
Person	Age	Tax Rate/Rebate
A	R13 635
B	R13 635
C	R21 114
D	R21 114
E	R23 607
F	R23 607

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]63[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]64[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]65[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]74[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]75[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]80[/TD]

</tbody>

Payroll Register​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E4[/TH]
[TD="align: left"]=IF($D4<='TAX Rates & Rebates'!$L$9,'TAX Rates & Rebates'!$N$9,IF($D4<'TAX Rates & Rebates'!$L$11,'TAX Rates & Rebates'!$N$10,'TAX Rates & Rebates'!$N$11))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Excel 2010

	L	M	N
Tax Rebate??
R13 635
R21 114
R23 607

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"][/TD]

</tbody>

TAX Rates & Rebates​

Thanks jtakw,

I appreciate your assistance a lot.

Regards,
Kyle

 

[KyleOliver]

jtakw, you are a LEGEND!!!!!

Your formula works FANTASTIC!!!!

THANK-YOU, THANK-YOU, THANK-YOU!!!

Regards,
Kyle

 

[jtakw]

jtakw, you are a LEGEND!!!!!

Your formula works FANTASTIC!!!!

THANK-YOU, THANK-YOU, THANK-YOU!!!

Regards,
Kyle

You're very welcome, happy it's working for you.