401k match excel help

[controllerneedshelp]

I need a formula that will calculate what the employer match will be
if the employee contributes 6% employer matches 3.5%
here is the tier match
EE ER match
1% 1%
2% 1.5%
3% 2%
4% 2.5%
5% 3%
6% 3.5%

Please help, I can figure it out manually but I need help so it will automate.

[jpg7n16]

Can the employee only contribute in whole percentages?

If so, use an IF function and a VLOOKUP function.

=IF(EE>6%,3.5%,VLOOKUP( -- insert the vlookup function requirements here using the chart of data you posted as a fixed reference in 'Table Array' --))

VLOOKUP - Excel - Office.com

EE = cell with employee's contribution

lookup_value = cell with your employee's contribution
table_array = the table you have posted above, make sure to use $'s on the numbers like $A$1:$B$7
col_index_num = 2 (the 2nd column in the table)
range_lookup = 0

[SeanH]

I apparently can't post images.

=if(E12>max(A:A),D12*max(B:B),vlookup(E12,A:B,2,fa lse)*D12)

In columns A and B indicate the small array of data you indicated above - namely the contribution and the corresponding match amount. Also include 0 contribution = 0 match. E12 = the employee contribution. D12= employee salary.