1. I must only use the max date values in the table I need the next highest value from (Table2) and

2. Table 2's "Schedule", "Pay Grade" and "Pay Step" equal "Schedule", "Pay Grade", and "Pay Step" in Table 1.

 

Challenge 1: I don't think that Table2 contains the Schedule, Pay Grade and Pay Step in any particular order. The logic, however, is that my result should return the next highest value when those 3 data fields are equal in both tables.

 

Challenge 2: The Schedule, Pay Grade, and Pay Step only go so high. e.g. Schedule "RN" on Pay Grade "RNB" goes up to Step 15, then the employee has reached the highest step and we no longer evaluate them. So I need to also incorporate a formula that will not show any employees if there is not a higher value for their Step.

 

I will be happy to post example of the data I already have, but wanted to see who would be interested in helping me out with this first.