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
[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Employee
[/TD]
[TD]Hours
[/TD]
[TD]Tenure
[/TD]
[TD]Type
[/TD]
[TD]Income
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]AAA
[/TD]
[TD]3
[/TD]
[TD]1-2
[/TD]
[TD]tech
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]BBB
[/TD]
[TD]3
[/TD]
[TD]New
[/TD]
[TD]tech
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]CCC
[/TD]
[TD]3.5
[/TD]
[TD]3-4
[/TD]
[TD]acct
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2 is the pay rate grid
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD]New
[/TD]
[TD]1-2
[/TD]
[TD]2-3
[/TD]
[TD]3-4
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]tech
[/TD]
[TD]10
[/TD]
[TD]10.25
[/TD]
[TD]10.50
[/TD]
[TD]10.75
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]acct
[/TD]
[TD]11
[/TD]
[TD]11.25
[/TD]
[TD]11.50
[/TD]
[TD]10.75
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]other
[/TD]
[TD]12
[/TD]
[TD]12.25
[/TD]
[TD]12.50
[/TD]
[TD]12.75
[/TD]
[/TR]
</tbody>[/TABLE]

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)