Using CONTAINS in INDEX/COLLECT cross-sheet formula

Steve@VHL

Can I use the CONTAINS function within an INDEX/COLLECT cross-sheet formula? I need to pull the Finish dates from a schedule for Tasks that contain a designated text string. There will be multiple tasks that contain that text string. Here is the formula that I have used so far, but I am getting an #INCORRECT ARGUMENT SET error right now. I appreciate any assistance!

Paul Newcome

Try this instead:

=INDEX(COLLECT({Finish}, {Task Name}, CONTAINS("Text String", @cell), {Level}, 1), 1)

Paul Newcome

Are you able to provide a screenshot and the formula for context?

Steve@VHL

@Paul Newcome Thanks for responding to my question. I intended to include the formula in my original post. Here it is: =INDEX(COLLECT({Finish}, CONTAINS("Text String", {Task Name}), {Level}, 1), 1). Here are definitions of some of the components of the formula:

• Finish: cross-sheet reference to Finish dates on a very long project schedule that contains repeating tasks for seven levels.
• Task Name: obviously, this is the Task Name column in the project schedule. The Milestones that I am trying to track are not an exact match with the Task Name--they are contained within but not the full Task Name (therefore, the CONTAINS function).
• Level: designation of what level (1-7) within the project schedule a task is.

Sending a screenshot won't really work, as this formula is part of a much bigger solution. It is part of a Metrics sheet that I am attempting to provide my client with the earliest Finish date and the latest Finish date across all levels.

It seems like it is a syntax question that I need to resolve and apply to multiple occurrences. I appreciate your input.

Paul Newcome

Try this instead:

=INDEX(COLLECT({Finish}, {Task Name}, CONTAINS("Text String", @cell), {Level}, 1), 1)

Steve@VHL

@Paul Newcome Thank you so much! That is exactly what I needed! I need to bone up on the @cell function. That seems to have been the missing piece here.

By the way, I will be in Seattle for ENGAGE and the Partner Summit. I hope to meet you while I am there. You are my most reliable resource here on the Community. I don't ask questions very often, but when I do, you seem to be the one I can count on to come through. Thanks again!