How to use Index Match on multiple criteria?

So, I found some articles explaining how to use Index Match on multiple criteria. However, when I try to do so I get a #Invalid Value error. My current formula is: =INDEX(COLLECT(Task:Task, [% Complete]:[% Complete], 100, Milestone:Milestone, 1), 1)

What I want Smartsheet to do is to show the task that has been marked as completed and that is checked as a milestone. See screenshot for more info.

formula 1.png

Find more posts tagged with

Accepted answers

All comments

Kelly Moore

Hey @Gijs

Your reference to 100 is incorrect - the %Complete column data is actually decimals and the number 1 is equal to 100%

=INDEX(COLLECT(Task:Task, [% Complete]:[% Complete], 1, Milestone:Milestone, 1), 1)

If there are more than 1 tasks that fit this criteria, you will need to add a JOIN to collect all of them.

JOIN(COLLECT(Task:Task, [% Complete]:[% Complete], 1, Milestone:Milestone, 1),",")

This would separate all the task names with a comma. Substituting a line break and then formatting as a wrap text cell/column would show all the task names stacked.

JOIN(COLLECT(Task:Task, [% Complete]:[% Complete], 1, Milestone:Milestone, 1), CHAR(10))

Will any of these work for you?

Kelly

Paul Newcome

I agree with @Kelly Moore. The issue is how you are entering the percentage.

That particular error is essentially the same thing as #NO MATCH when using an INDEX/MATCH. Basically what is happening is the COLLECT function is pulling in zero rows, so the 1 in the second portion of the INDEX function is an invalid value since there is not at least 1 row being pulled in by the COLLECT function.

Gijs

@Kelly Moore Thanks a lot for the feedback. It looks to be working :).

However, I was wonder if there would also be a possibility that the formula picks the latest milestone passed. E.g., if you now have two milestones checked as milestones and they are both 100%, then they would both be shown in the cell. Would there be a way of tracking it via na 'actual date completed' column?

For example:

Milestone A, completed, at 12 January

Milestone B, Completed, at 13 January

Milestone C Completed, at 11 January.

Then it should display that Milestone B is the last passed Milestone, and not Milestone C as it is the last one in the row order (hoping that the Project Managers correctly fills in the actual date completed columns.

Or what would be a best practice (fool prove practice) to use?

Kelly Moore

Hey @Gijs

Yes, we should be able to choose the latest milestone. A question for clarity. Using your screenshot as a reference - You have a Parent row called Milestones and that is the row checked off. It has 4 children under the Parent Row, none of the children are checked off but the first child is at 100%. When you ask above about finding the date from the latest Milestone - are you considering the checked Parent Row as a Milestone, MilestoneA for example, or are the children the different Milestones?

Gijs

@Kelly Moore thanks, yes the screenshot is not completely showing how it would be in practice. Normally the milestones row isn't checked off, only milestones 1, 2, 3, and 4 (in the case of the screenshot). However, I obtained feedback from the project managers that they would like to have the milestones sprinkled in within the project plan and not have it as a separate list (child).

So, it would be needed to only display the LAST milestone that was met (not the chronological order the milestones are displayed throughout the project plan)

Kelly Moore

Hey @Gijs

If how I envision your dataset is correct, you should be able to find the most recent Milestone using an Index/Match where the Match contains the Max/Collect of the date. Out of curiosity, how is your Milestone checkbox getting checked? Duration = 0? Manually?

You mentioned needing the Project Managers to input the correct date for their task. If a specific trigger can be identified that indicates completion, consider using the Record Date automation to automatically record the date.

If your sheet is dedicated to just one project:

=INDEX(Task:Task, MATCH(MAX(COLLECT([End Date]:[End Date], [End Date]:[End Date], ISDATE(@cell), [% Complete]:[% Complete], 1, Milestone:Milestone, 1), [End Date]:[End Date], 0))

If you have multiple projects in the same list then we will have to create a helper date column to find the specific max date for a project group and then reference that group of project rows against that date. You would use the MAX(COLLECT for the date - it would also include the project name.

=MAX(COLLECT([End Date]:[End Date], [End Date]:[End Date], ISDATE(@cell), [% Complete]:[% Complete], 1, Milestone:Milestone, 1, Project:Project, Project@row)

*Substitute Project with the project name, project ID, etc- however you have it identified in your sheet

Then, as your desired formula

=INDEX(COLLECT(Task:Task, [End Date]:[End Date], ISDATE(@cell), Project:Project, Project@row, [End Date]:[End Date], @cell=[Helper Date]@row,[% Complete]:[% Complete], 1, Milestone:Milestone, 1), 1)

Will this work for you?

Kelly

Gijs

@Kelly Moore, thanks a lot for the help so far. I tried out this formula:

=INDEX(Task:Task, MATCH(MAX(COLLECT([End Date]:[End Date], [End Date]:[End Date], ISDATE(@cell), [% Complete]:[% Complete], 1, Milestone:Milestone, 1), [End Date]:[End Date], 0))

I will look into it, why this is the case and try out some stuff as well. I think I want to drop the milestone column and substitute it with when the duration is set to 0 (to reflect their already way of working in Microsoft Project). So the requirements would be to show the text of the milestone from 'Task' whenever the 'duration' has been set to 0, '% complete' is as 100%, and the 'end date' is the latest end date

Capture formula.PNG

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.