array lookup calculation

[uwfenske]

I am having troubles getting a payroll calculation formula set up. We have annual increases for employees (tenure) and I need to factor in different pay rates for different job types.

Basic layout of Sheet 1

A	B	C	D	E	F
1	Employee	Hours	Tenure	Type	Income
2	AAA	3	1-2	tech
3	BBB	3	New	tech
4	CCC	3.5	3-4	acct

<tbody>
</tbody>

Sheet 2 is the pay rate grid

A	B	C	D	E	F
1		New	1-2	2-3	3-4
2	tech	10	10.25	10.50	10.75
3	acct	11	11.25	11.50	10.75
4	other	12	12.25	12.50	12.75

<tbody>
</tbody>

What I am trying to get is a formula that will find the pay rate when it matches the tenure and the job type, then multiply that rate times the number of hours.

 

[Tetra201]

If the job types and tenures are unique, try

=B2*SUMPRODUCT((C2=Sheet2!$B$1:$E$1)*(D2=Sheet2!$A$2:$A$4),Sheet2!$B$2:$E$4)