If Formula with 20 Possible Outcomes
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
-
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 - 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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!