nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Simplifying Index / Match function

aecross

Hi Community!

My goal is to have a formula that indexes the "Status" column in my 2.0 WMTN sheet; i have built this formula however the below is only referencing 1 of 5 sheets, so i will need to recreate all the IF functions 5 times over…this isn't very efficient and there's a chance a new task can be added, which then requires updates to this formula any time a task change happens.

=IFERROR(IF(A@row < 3, "", IF([task]@row = "ABCT001", INDEX({2.0 - Metric Sheet.DD Complete}, MATCH([CU Project ID]@row, {2.0 - Metric Sheet.Project ID}, 0)), IF([task]@row = "ABCT002", INDEX({2.0 - Metric Sheet. Survey}, MATCH([CU Project ID]@row, {2.0 - Metric Sheet.Project ID}, 0)), IF([task]@row = "ABCT003", INDEX({2.0 - Metric Sheet. Survey}, MATCH([CU Project ID]@row, {2.0 - Metric Sheet.Project ID}, 0)), IF([task]@row = "ABCT004", INDEX({2.0 - Metric Sheet.As-built complete}, MATCH([CU Project ID]@row, {2.0 - Metric Sheet.Project ID}, 0)), IF([task]@row = "ABCT005", INDEX({2.0 - Metric Sheet.TF Complete}, MATCH([CU Project ID]@row, {2.0 - Metric Sheet.Project ID}, 0)), IF([task]@row = "ABCT006", INDEX({2.0 - Metric Sheet.TF Complete}, MATCH([CU Project ID]@row, {2.0 - Metric Sheet.Project ID}, 0)), IF([task]@row = "ABCT008", INDEX({2.0 - Metric Sheet.base model complete}, MATCH([CU Project ID]@row, {2.0 - Metric Sheet.Project ID}, 0)), IF([task]@row = "ABCT011", INDEX({2.0 - Metric Sheet.SD2 start}, MATCH([CU Project ID]@row, {2.0 - Metric Sheet.Project ID}, 0)), IF(OR([task]@row = "ABCT012", [task]@row = "ABCX003"), INDEX({2.0 - Metric Sheet.handoff}, MATCH([CU Project ID]@row, {2.0 - Metric Sheet.Project ID}, 0)), IF(OR([task]@row = "ABCT012", [task]@row = "ABCX003"), INDEX({2.0 - Metric Sheet.SD2 handoff}, MATCH([CU Project ID]@row, {2.0 - Metric Sheet.Project ID}, 0)), IF(OR([task]@row = "ABCT018", [task]@row = "ABCX004"), INDEX({2.0 - Metric Sheet.bid set }, MATCH([task]@row, {2.0 - Metric Sheet.Project ID}, 0)), IF(OR([task]@row = "ABCT012", [task]@row = "ABCT027", [task]@row = "ABCX005"), INDEX({2.0 - Metric Sheet.start}, MATCH([CU Project ID]@row, {2.0 - Metric Sheet.Project ID}, 0)), IF([task]@row = "ABCX006", INDEX({2.0 - Metric Sheet.photo review}, MATCH([CU Project ID]@row, {2.0 - Metric Sheet.Project ID}, 0)))))))))))))))), "N/A")

I believe there is a simpler way to create this formula by using Index, Collect, and Match, but i'm not quite sure how to formulate it.

I want to Index/Collect the Status column in 2.0 WMTN sheet with whichever status matches the "Task" in my current sheet (projections) to the "Task" in the 2.0 WMTN sheet, this way i won't have to list out every single possible task, it will just find the match in the sheet.

Appreciate any help with configuring this formula!

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Try this instead:

IF([Intacct Task@row <> "", IFERROR(IFERROR(INDEX(COLLECT({2.0 - SC.Complete Date}, {2.0 - SC.Project ID}, @cell = [CU Project ID]@row , {2.0 - SC.Task ID}, @cell = [Intacct Task]@row), 1), INDEX(COLLECT({2.0 - WMTN.Complete Date}, {2.0 - WMTN. Project ID}, @cell = [CU Project ID]@row, {2.0 - WMTN.Task ID}, @cell = [Intacct Task]@row ), 1)), ""))

All comments

Paul Newcome

Are you able to provide some screenshots for context?

aecross

@Paul Newcome

Here is the projections sheet that the formula will live in. I will eventually add the formula to the "Task Complete Date" column too but right now just focused on the "Task Status". Ideally, the "Task Status" is pulled by matching it to the row in "2.0 WMTN" that has the same "Intacct Task" AND "CU Project ID".

Here is the sheet where the data lives and is updated that i want to pull from:

Paul Newcome

Try this:

=INDEX(COLLECT({Source Sheet Status Column}, {Source Sheet Project ID Column}, @cell = [CU Project ID]@row, {Source Sheet Task ID Column}, @cell = [CU Task ID]@row), 1)

aecross

You are a genius, thank you, Paul!!!!

I, of course, have one more piece to add….

I have multiple sheets that i need to compile this data from AND i want to account for any Task ID cells that are blank (they are returning a value, i think based on the Project ID). When i use the below formula i get #INCORRECT ARGUMENT SET

=IF(ISBLANK([Intacct Task]@row), "", IFERROR(INDEX(COLLECT({2.0 - SC.Complete Date}, {2.0 - SC.Project ID}, @cell = [CU Project ID]@row, {2.0 - SC.Task ID}, @cell = [Intacct Task]@row), 1), ""), IFERROR(INDEX(COLLECT({2.0 - WMTN.Complete Date}, {2.0 - WMTN. Project ID}, @cell = [CU Project ID]@row, {2.0 - WMTN.Task ID}, @cell = [Intacct Task]@row), 1), ""))

Paul Newcome

Try this instead:

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.