formula to adjust a rate based on lookup of dept.

[PitchNinja]

Hello, I am looking for a formula that will tell me a person's base rate of pay and subtract any differentials received. Tab 1 contains the person's employee number, name, dept code they work in, and the job type. Job type is mostly irrelevant with the exception of any job type that has "FR" in it. The differentials received are based mostly on a person's department with the exception of any job that contains "FR". In the sample file on Tab 2, the formula I am looking for would go in cell F1. F2 has their rate. I just typed in a rate here in the sample, but in the actual sheet it is a formula that looks up their true rate of pay after all differentials are received. Cell range G1 thru H14 have the pertinent info as far as which departments get differentials if any. Here is where FR comes into play - if anyone has "FR" in their job type, they get an extra $.15 added onto whatever differentials they are receiving from that department. Any other job type does not get anything extra

So I want the formula in F1 to take the rate that is in F2 and subtract it by any differentials coming from their department, and job type if applicable.

So for a couple examples:
*Smith, John 3 is in a department that gives a $.10 differential. His rate in F1 should be $24.90
*Smith, John 6 is in a department that gives a $.30 differential. He is also in a job type that has "FR" in it so he gets an extra $.15. His rate in F1 should be $24.55

I thought maybe an index match formula would be used for this but wasn't really able to come up with the syntax for it since it is really a multiple criteria lookup so maybe it wouldn't be an index match?

Can this be done based on the info that is in the sheet?

[protonLeah]

Adj_Rate ='Tab 2'!$F$2
Emp_No ='Tab 2'!$B$1
-------------------------------------

Please Login or Register  to view this content.

[PeteABC123]

Hi PitchNinja:

Try this in F2

Please Login or Register  to view this content.

Let us know if this effort is solved.

Pete

[PitchNinja]

Thank you both. I ended up using the first one because I had seen it first. I have tested it in my actual sheet and it will absolutely work for what I need it to do. Thank you so much - gave you both reps for your help!