Multiple IF(AND(OR statements to return a value

Kenny T.

I am trying to find the correct formula for the following:

IF classification is External Hire, AND Business Title is Assistant Store Manager OR Store Manager, AND Store Format is Steady State, THEN training duration = 42

IF classification is External Hire, AND Business Title is Assistant Store Manager OR Store Manager, AND Store Format is Pre-Launch, AND Geo is Existing OR Extended THEN training duration = 49

IF classification is External Hire, AND Business Title is Assistant Store Manager OR Store Manager, AND Store Format is Pre-Launch, AND Geo is New THEN training duration = 52

IF classification is Internal Hire, AND Store Format is Pre-Launch or Steady State, AND Geo is Existing OR Extended THEN training duration = 42

Brent Wilson

@Kenny T.

I like to say Nested IF's are like cutting a block of Cheese..

You go through all the scenarios cutting off bits as you go through all the scenarios

The interpretations are..

Note: I made an assumption about New Hires that are not "Managers" in the second case

The if statement that is built becomes

=IF([External Hire]@row = "No", 42, IF(OR([Business Title]@row="Assistant Store Manager",[Business Title]@row="Store Manager"), IF([Store Format]@row = "Steady State", 42, IF([Geo]@row="New", 52, 49)), 42))

Now for a more advanced class

In cases like these, I like to use VLOOKUPS with a Matrix.

I would create a training matrix Grid with a Helper Column

The helper column is pretty simple with one quirk as External Hire is a checkbox, I could use a Yes or no but then I also need to account for the user leaving it blank which creates the 3rd scenario

So in the case of the helper column, I Simply make one big text string

=IF([External Hire]@row = 1, "Y", "N") + [Business Title]@row + [Store Format]@row + Geo@row

You can see how I had to do a bit of logic on the External hire to switch it to a Y or N

Now I create the same helper column in the Data Sheet

Then I link the Data sheet to the Matrix with a VLOOKUP

=VLOOKUP(Helper@row, {Training Matrix}, 6, false)

so this matches the two helper rows and then looks up the training duration (stored in column 6 of the matrix)

Now you can see a problem down with Chad Brooks or Brian Jones as those Scenarios are not in the Traning Matrix..

You could load all of them in or make an assumption that if they are not in the table they should all get 42 units of training.

So Say Chad Brooks as a Sales Associate at a Pre-Launch New store would get 42 units, same as David Brooks an Internal Hire Store Manager at a Pre-Launch Extended Store

The Hybrid formula that would do this is below and you can see the results in the Training Duration HYBRID column

=IF(ISERROR(VLOOKUP(Helper@row, {Training Matrix}, 6, false)), 42, VLOOKUP(Helper@row, {Training Matrix}, 6, false))

Basically, if there is an error, which you can see in the VLOOKUP column as a #NO MATCH ,we will assume 42 units

The beauty in this is that as your scenarios change..

Someone says Hmm no David Brooks should have 49 units of training even if he was an internal hire you simply have to create that in the matrix and it all works

and Voila

Hope this gives you more than you need and doesn't blow your brain !!