If Formula with 20 Possible Outcomes

Options
aecross
aecross ✭✭
edited 04/12/23 in Formulas and Functions

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!

Answers

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

    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?

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • aecross
    aecross ✭✭
    Options

    @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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!