Multiple nested if than statements thinking vba maybe direction to go

I have hit a brick wall whether because I am rusty or simply cannot wrap my head around this. I am trying to build a excel workbook to calculate employee's merit increases to their annual income. To do so I must take both the COMPRATIO which is a figure provided, as well as their individual employee appraisal score use a matrix to come up with what percentage they will receive.
The matrix looks like this
PERFORMANCE RATING <91.01 >91.01AND<97.00 <97.01 AND <103 >103.01 AND <109 Greater than 109.00
3.5-4.0 6.00% 5.00% 4.00% 3.00% 3.00%
3.0-3.49 5.00% 4.00% 3.00% 3.00% 2.00%
2.5-2.99 3.00% 3.00% 2.00% 1.00% 0.00%
2.0-2.49 2.00% 1.00% 0.00% 0.00% 0.00%.

I am not partial to how this is accomplished just that it is accomplished. Currently the work book is set up with a tab for all of the information called MASTERROSTER, and a tab called Merit Matrix that stores only the matrix. The cells holding their Comp Ratio is column X, the Performance Rating is in column O, and their Annual Rate is in column W all on the MASTERROSTER TAB. The end result of all the logic would place the amount in Column Q. I am totally fine with the logic not calculating the final amount as long as it spits out the percentage of the increase. The task that is murdering my brain is writing the 19 Plus If statements or how to write the VBA for it. I am an access guy who is being forced to use VBA in excel something I haven't done in probably 10 years. Please Help!

You can use INDEX/MATCH/MATCH to return the value at the intersection.
Post some sample data in a workbook, including the performance rating matrix above and we can show you how.

BSB

In the attached is a quick example of how to use INDEX/MATCH/MATCH for this purpose.
You should be able to amend this to fit your data.

BSB

Here is kind of a dumbie version of the much larger workbook I am working on. Master roster is not only used to input raw data but also to display the calculated data. The Merit percentages (Sheet1.Column M) are calculate via using the Merit Matrix and COMPRATIO(Sheet1.Column U) and Appraisal score (sheet1.Column K) to plug the percentage into Sheet1.column M. PFP Percentage is a bit more difficult the exempt matrix is for all salary labeled employees (Sheet1.Column Q) and uses the goal score (Sheet1.ColumnL) and plugs into sheet1.columnN. While the nonexempt matrix is for hourly employees who do not have job titles containing the "MSS" or "MSR" labels. I am sure I am making this more difficult in my own head then I need to be.Master PFP Merit List.xlsx

Hopefully the attached is somewhere near what you need, although it may need a little tinkering if I've misunderstood your need.

Anything I've added is highlighted in green. I've had to reverse the order of your matrices in order to make this work as INDEX with non-exact match required them to be in a specific order.

BSB

The Merit calculations and the PFP calculations for the exempt works perfectly. I do have a question or two for both my own knowledge and for further understanding. What is the difference between using the IFERROR with the index and matching as opposed to just using the index and matching for the Merit calculations? For the PFP Non-exempt is there a way to only calculate PFP for employees who show as hourly in column Q, and if their job title in column C does not contain "MSS" or MSR?

Originally Posted by clpickett3

What is the difference between using the IFERROR with the index and matching as opposed to just using the index and matching for the Merit calculations?

All IFERROR is doing here is leaving the cell blank if an error occurs. For example, in row 2 there is no value in column K. This results in an error as the INDEX/MATCH/MATCH needs two numbers to find the intersection in the matrix.

Originally Posted by clpickett3

For the PFP Non-exempt is there a way to only calculate PFP for employees who show as hourly in column Q, and if their job title in column C does not contain "MSS" or MSR?

What do you want the formula to do if they're Hourly and DO have MSS or MSR in their job title?

BSB

Is this what you need?

BSB

If they are hourly and do have the MSS or MSR in their job title then nothing needs to be done as they do not receive the bonus being calculated.

Originally Posted by clpickett3

If they are hourly and do have the MSS or MSR in their job title then nothing needs to be done as they do not receive the bonus being calculated.

Which is what the file in post #8 does(?)

BSB

Im blind, thank you so much for your help and the information to educate myself

No problem. Happy to help

Don't forget to mark the thread as SOLVED if you're happy you have a working solution.

BSB

Ok I messed something up here. the merit increase formula works perfectly(column y) the PFP formula is creating NAs where good data should be. TEST.xlsx

The last INDEX/MATCH in your workbook is looking at column L rather than X. Change it as per the red highlight below and see if that works for you.

This is the formula from row 5. The first one showing an N/A.

Formula:

Please Login or Register  to view this content.

BSB

Awesomesauce how do I mark it solved?

found another problem its not a huge problem but one individual instead of it plugging in a zero its causing an error thus a blank space. the persons column l is a 99.60 and the person's appraisal score is a 1.92. what would cause the error rather than the zero?

Midnight here so I've just shut down my laptop. Responding from iPad so cannot open Excel docs.

Try stepping through the formula using the evaluate tool built into Excel. That will show you where it errors.

I shall give it another look in the morning.

BSB

I will try thank you for everything