IF Formula

Setting up a formula where Tier I = 1500, Tier II = 1200, otherwise it's 1000. This formula setup works for me with in-sheet references, but not cross-sheet references... not sure how to adjust for the cross-sheet.

=IF({MASTER TRACKING SHEET Range 1}@cell = "Tier I", "1500", IF({MASTER TRACKING SHEET Range 1}@cell = "Tier II", "1200", "1000"))

Thank you!

Find more posts tagged with

Accepted answers

All comments

Dale Murphy

@aecross Not sure your problem description is complete. What is it in the sheet with the formula that matches to something in the cross-referenced sheet? You need to make that connection, don't you? You may need to build an index formula that connects the cross-ref range to the row of the sheet you are in.

aecross

@Dale Murphy

I've indexed this correctly and it pulls in the Tier level. My next question is how to incorporate the IF statement into an index formula? With the below i am getting an unparsable error.

=IF(INDEX({MASTER TRACKING SHEET Range 6}, MATCH([JOB #]@row, {MASTER TRACKING SHEET JOB NUMBER}, 0), {MASTER TRACKING SHEET Range 1}@cell = "Tier I", "1500", {MASTER TRACKING SHEET Range 1}@cell = "Tier II", "1200", "1000"))

Dale Murphy

@aecross You want your IF to do this: IF (<the INDEX statement, that returns some result> EQUALS <your condition of "Tier 1"> COMMA ...

I can see that the INDEX statement doesn't close properly. I think you need a closing bracket here (at the end):

IF(INDEX({MASTER TRACKING SHEET Range 6}, MATCH([JOB #]@row, {MASTER TRACKING SHEET JOB NUMBER}, 0))

and then you need to evaluate what it returns against your condition.

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.