Index/Match? Not sure best function for my needs

Annie Dunn

Hello,

I have a very extensive sales tracker that I use to log charges and calculate the sales rep's commissions based on those charges. The commission percentages change depending on a variety of scenarios, so I created four columns with separate formulas for each scenario. I need the commission column to auto-populate with the output data from the correct scenario by looking at two or three factors in the sheet to determine the right scenario.

Here's a screen shot of the sheet:

For simplicity I'm focusing on one rep with initials "JK"; as you can see there are four columns with her scenarios:

JK Commission, JK + House Commission, JK DME Commission, and JK + House DME Commission

(Keep in mind there are 6 reps total, so that's 24 total scenarios.)

In the past I was using "IF" statements to specify what data to use to determine the right scenario, but the formulas were getting really long and difficult to edit, so I'm wondering if there's a better way.

More specifically what I need is for all the "Commission" column cells to populate in the following ways:

If the ACCOUNT OWNER is "JK" and the SALES REP is "JK", pull data from "JK Commission"

If the ACCOUNT OWNER is "MB" and the SALES REP is "JK", pull data from "JK + House Commission"

If the ACCOUNT OWNER is "JK" the SALES REP is "JK", and the BILLED BY is "Delta Medical, H&L Medical, See The Trainer, or Medequip", pull data from "JK DME Commission"

If the ACCOUNT OWNER is "MB" the SALES REP is "JK", and the BILLED BY is Delta Medical, H&L Medical, See The Trainer, or Medequip, pull data from "JK DME Commission"

(Right now I'm using the BILLED BY column to let me know if the order falls into the DME category, but I also considered creating a check-box column that is checked if DME and unchecked if not.)

Is there a way to search the whole sheet so the commission cells automatically look for the matching criteria above without having to write out 24 "IF" statements and dragging that formula into the whole sheet?

For all 24 scenarios, the DOS (date of service) should be greater that Jan. 1, 2020.

Let me know if I need to clarify anything - thank you!!!

-Annie

Paul Newcome

DO you have those for columns for the other reps as well?

Annie Dunn

I am in the process of building them, but I wanted to make sure this would work with one before I did the rest.