Question about IF THEN and refernceing other tables

[ExcelNovice2013]

I have a workbook (named Pay Ranges) with pay grades 1 -25 and 3 pay rates for each grade based on years in title. In another workbook (named Scenarios) I am running scenarios based on data in the Pay Ranges workbook. The scenario workbook contains the employee name, current pay grade and years in title. The Pay Ranges workbook is maintained separately and is a living document that is updated when the dollar value of the ranges change.

I need a formula in the Scenario Workbook to look at the employee's current years in title and determine which rate of pay they would be eligible for based on grade then years in title. The rates change every 5 years.

The formula I started with was IF nesting the different rates but I can't get my head around the need for the formula to consider the pay grade first. I am getting a circular reference <sigh>. Any help, suggestion, lifelines, or other forms of assistance is truly appreciated!!!

 

[Comfy]

I use INDEX & MATCH most of the time, but there are other ways to do it.

If you could post the layout of your workbooks I could provide you with a formula.

 

[hemesh]

Hello, Can you please upload your sample data