If Formula with 20 Possible Outcomes

aecross

Hi there -

I need assistance in creating a formula. My goal is determine the Estimated OSC depending on the MEP Partner, Structural Partner, and the Overall Tier (this column is in another sheet). However, because I have multiple MEP/Structural partners and tier levels, the number of different combinations of OSC's I could have is 20. I'm not sure if it's best to create a formula that encompasses all 20 possible options, or if there is a better way to go about this.

So far, here is what I have:

=IF([MEP PARTNER]@row = ABC, [STRUCTURAL PARTNER]@row = ABC2, {MASTER TRACKING SHEET Range 1}@cell = TIER I, "100", OR([MEP PARTNER]@row = ABC, [STRUCTURAL PARTNER]@row = ABC2, DT REFRESH - MASTER TRACKING SHEET Range 1}@cell = TIER II, "75"))

If MEP = ABC and Structural = ABC2 and Overall Tier = Tier I, then OSC = 100 ... BUT if MEP = ABC and Structural = ABC2 and Overall Tier = Tier II, then OSC = 75.

I appreciate any and all help!

Ryan Sides

Hi there. What if you created a sheet that combines your combos in one column and then has the Osc in the second column? Then you could create a helper column on your current sheet to combine the 3 and use an index/match to look up the osc for that combo. Like…

key sheet:

Key Osc

ABCABC2I 100

ABCABC2II 75

your current sheet:

key column = [MEP PARTNER]@row + [STRUCTURAL PARTNER]@row + [Estimated OSC]@row

Then add cross sheet references back to your key sheet.

what do you think?

aecross

@Ryan Sides - thanks for the help! I've realized my original idea was too complicated so I'm working through something similar to your recommendation using the index formula.

Thanks again!