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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!