Variables for formulas

[ddietrich]

I'm creating spreadsheet with info:

Job Title, Years of Service (YOS), Pay Rate

I want to be able to have the pay rate fill automatically based on the Job Title & Years of Service. The Pay rate chart has columns for years of service & different rates by year. there are separate pay rate charts for different Job titles:

Job YOS Rate
A 1 $1
A >1 $2

B 1 $3
B >1 $4

Is there a formula I can enter for the pay rate cell by individual that will pull the correct data from the chart
i.e. Employee 1 has Job A, YOS=1 so pay rate for Employee 1 =$1
Employee 2 has Job B, YOS =15 so pay rate for Employee 1 =$4

[TMS]

You need to share a sample of the workbook.

[ddietrich]

See attached sample workbook

[TMS]

Is there a reason the Tables are laid out like they are? Or are you open to suggestions?

[ddietrich]

Open to suggestions!

[TMS]

OK, try this ... see updated example workbook.

[TMS]

Sorry, should have said: I left the original Rate Table in Sheet1 AND copied it to a new Rates sheet for reference. The formulae ONLY use the new Rates Table. I defined some Named Ranges for the Rates, Jobs and Years in the NEW table.

[ddietrich]

Thanks for your help! I set up a similar table based on what you sent but have to spend some more time on it