Need help with multiple conditions, from multiple ranges, on multiple sheets.

[csulbmike]

Hey gang, I'm having a devil of a time trying to sort out a formula to pull data from multiple tabs with multiple if conditions.

The attached worksheet has one sheet for employee names, their years of service, classifications and salaries. The other has the classifications with the pay ranges for those positions. What I'm trying to do if create a formula that will compare what they are earning (Column F) to what they should be earning based on their experience (Column P). Each employee classification has a pay range, broken into four tiers, based on experience. 0-4 yrs service is Tier 1. 5-9 = Tier 2. 10-14=Tier 3, 15+ = Tier 4. However, people should be getting a raise to the halfway point of the Tier after 2.5 years service in that Tier. For example, if Tier 1 pay from $3-4k per month, an employee with 3 years service would be getting paid $3,500.

The employee's experience and qualified tier will create a "modifier" that should increase their salary by that factor. Using the example above, the modifier is 0.5. So, the formulas that exist currently in the spreadhseet take the Tier 1 pay range $3-4k, and multiply the range ($1k) by the modifier, coming up with $500. Add $500 to the base pay of $3000 and that's where you get the $3,500.

The way I have the attached sheet setup is that the formula I need your help creating will determine what the modified salary is and play it in Columns L through O. Here is the basis of the formula:
1. the formula has to search Sheet 1, Column D and Column E and match it to the Ranges for those job class and ranges on Sheet 2.
2. Then, the formula would pull multiply the Modifier (Sheet 1, Column K) by the salary range (Sheet 2, Column I, K, M, or O). The salary range and column that it will pull data from is based on Sheet 1 Column H

So, on Sheet 1, Columns L thru O, if the employee is a Qualified Tier 1 employee (Column H), Column L would multiply Sheet 1 Column K times Sheet 2 Column I, pulling the data from whatever row corresponds to the appropriate Class Code (Sheet 1, Column D) and Range (Sheet 1, Column E) for the employee.

I'm not married to how the columns are setup; it's just the only way I could think of doing it.

Any thoughts? Any help would be welcome.

[FDibbins]

Hi, welcome to the forum

1 quick way to ID the Code and Tier would be to add a helper column and combine them
Alpha sheet...
=D1&" "&E1

OR if there will only ever be 4 tiers and a 4-digit Code, skip the " " and just combine them - that would actually make reading the code simple, too...
=d1&C1
10351
10352

You could do the same on the Monthly sheet - but add that helper (which you can hide if you want) to the very 1st column

Then use vlookup to find what you need.