vlookup with multiple criteria

dcarey1

New Member
Joined
Jun 26, 2018
Messages
3
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,
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Forum statistics

Threads
1,223,895
Messages
6,175,257
Members
452,625
Latest member
saadat28

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