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!

Find more posts tagged with

Accepted answers

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.