How to analyze multiple conditions in a row, return an answer in the same row, new column

I'm trying to analyze multiple cells in any row of data, have a look-up of either a value based on a term in the same row, and return a value (either a flat amount, or sometimes needing a computation) in the same row in a new column.

For example, each person has multiple tasks to perform, and can be paid a commission based on the task definition, but each defined task pays a different commission.

The logic in this example is in column D, with results placed in column E.

Grid3.JPG

My challenge is there are up to 40 different people with multiple commission rates based on up to a half dozen tasks, so each line can yield a completely different commission computation. I'm pretty good at using nested if statements, but I suspect something this complex will need more knowledge that I currently have! I don't know how to attack this as there seems to be far too many variables then simple if statements or something similar can compute. I'm looking for anyone who can point me in the right direction. If i can understand how to build something for just one person, I need to be able to do the same adding multiple people and multiple computational variable.

Thanks in advance for any assistance.

Bruce

What are the rules governing who is paid what?

Can you post a file with clear explanation of the rules (logic) for assigning commission.

Problem suggests one or more Table "lookups".

Hi John,

I appreciate your asking for the rules governing the payments. The Attachment to my reply has one such table already constructed (and I put an image of the key logic used in the table, below so others can see an example of what it is). For the task name across the top of the chart, below in those columns are the payment amounts for the people on the left. All are "flat dollar amounts" as seen, so no computation is needed - those values simply need to be returned to the main worksheet. The only column with a real computation is column H labeled "Appraisal", and the commission percentages in that column would be applied against the dollar amount to compute the needed result.

For simplicity sake, the only detail I do not show is another half dozen or so task descriptions (column H), but I haven't listed them (except "Appraisal"), because they all would have the same commission percentage applied, regardless of their name.

I made a few attempts at nesting "And", or "Or" in If statements, I've tried using "Sumif" with a "vlookup", but each cell would need such a huge string of nested items, it would be way too difficult to edit as new people and commissions are added. So, yes, I think your looking to a table or table would be the way to go, and hopefully this reply gives you enough to fully understand the logic involved and for you to attempt a solution. I REALLY appreciate your taking the time to help!

Bruce

Grid4.JPG

John,

A personal thanks for the help in logic - I didn't use your exact thoughts, but I did end up going to a vlookup solution with some changes in how I was trying to track commissions. In any event, I have what my final result is in the edited post...thanks again...

I would not like the responsibility of maintaining your IF statement!

Looking at the statement your AND statements are the wrong construct...

=IF(AND(condition1,condition2 ,,,,,,,),TRUE result, FALSE result)

NOT

=IF(AND(condition1,AND(condition2 )),TRUE result, FALSE result)

I am sure the statements can be improved: hard coding names does not appear a good approach.