Driving Table TL_Lookup
Empl # Name Trad calc Method Roth calc Method AGE Trad Amt Roth Amt Rate
004 SMITH,JOHN Gross Pay 36 22 25
076 CORTEZ,JANELLE Default 49 140 16.5
137 JONES,MICHAEL Gross Pay 55 18 36.5
195 FRANKLIN,THOMAS Default 60 225 24.11
Named Ranges
[TABLE="width: 198"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Irs Limits[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Great_50[/TD]
[TD="align: right"]24500[/TD]
[/TR]
[TR]
[TD]Less_50[/TD]
[TD="align: right"]18500[/TD]
[/TR]
</tbody>[/TABLE]
Annual Hours 2080
Pay Periods 52
Need a formula to:
vlookup TL-Lookup find the age if <50 than the max contribution is 18,500, if >= 50 max is 24,500
vlookup TL_Lookup trad Method, if "Gross Pay" Rate*Annual Hours*(Trad Amt/100). If trad method = "Default" pay periods*Trad Amt = Trad Contribution Amount
vlookup TL_Lookup Roth Method, if "Gross Pay" Rate*Annual Hours*(Roth Amt/100). If Roth method = "Default" pay periods*Roth Amt = Roth Contribution Amount
Total Contribution = Trad Contribution + Roth Contribution
Thanks,
Empl # Name Trad calc Method Roth calc Method AGE Trad Amt Roth Amt Rate
004 SMITH,JOHN Gross Pay 36 22 25
076 CORTEZ,JANELLE Default 49 140 16.5
137 JONES,MICHAEL Gross Pay 55 18 36.5
195 FRANKLIN,THOMAS Default 60 225 24.11
Named Ranges
[TABLE="width: 198"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Irs Limits[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Great_50[/TD]
[TD="align: right"]24500[/TD]
[/TR]
[TR]
[TD]Less_50[/TD]
[TD="align: right"]18500[/TD]
[/TR]
</tbody>[/TABLE]
Annual Hours 2080
Pay Periods 52
Need a formula to:
vlookup TL-Lookup find the age if <50 than the max contribution is 18,500, if >= 50 max is 24,500
vlookup TL_Lookup trad Method, if "Gross Pay" Rate*Annual Hours*(Trad Amt/100). If trad method = "Default" pay periods*Trad Amt = Trad Contribution Amount
vlookup TL_Lookup Roth Method, if "Gross Pay" Rate*Annual Hours*(Roth Amt/100). If Roth method = "Default" pay periods*Roth Amt = Roth Contribution Amount
Total Contribution = Trad Contribution + Roth Contribution
Thanks,