Nested Index/Match if Partial Text

EmilyH

I require a cross-sheet formula that will search for a Task (row) in another smartsheet, if the Task ‘Comment’ (column) contains the word “All”, then go to another Task row and index the Date. If the Task ‘Comment’ (column) does not include the word “All”, display the original Task Date.

For example:

If Task 4 Comment contains “All” (upper or lower case) within the text string, then display the date for Task 9 = 12/18/21

If Task 4 Comment does not contain the word “All” (upper or lower case) within the text string, then display the date for Task 4 = 12/27/21

Any ideas on how to make it work? Thanks for your help!!!

Devin Lee

Hey @EmilyH,

See if you can modify this to get what you are looking for. You will have to replace the column references with cross-sheet references but it should work to pull in either the date from the matching task or if the word "all" is in the comments it will pull from Task 9. Not sure if Task 9 is always going to be the reference date but this is set up that way.

Standard formula:

=IF(CONTAINS("all", INDEX(Comment:Comment, MATCH(Task@row, [Task ID]:[Task ID], 0), 0)), INDEX(Date:Date, MATCH("Task 9", [Task ID]:[Task ID], 0), 0), INDEX(Date:Date, MATCH(Task@row, [Task ID]:[Task ID], 0), 0))

Replaced with References

=IF(CONTAINS("all", INDEX({Ref 1:Comment:Comment}, MATCH(Task@row, {Ref 2: [Task ID]:[Task ID]}, 0), 0)), INDEX({Ref 3: Date:Date}, MATCH("Task 9", {Ref 2: [Task ID]:[Task ID]}, 0), 0), INDEX({Ref 3: Date:Date}, MATCH(Task@row, {Ref 2: [Task ID]:[Task ID]}, 0), 0))

ChelseaH

Hi EmilyH!

It sounds like the CONTAINS function could be useful here, but I don't understand why, in your example, you want the date for Task 9 to return if Task 4 contains 'all'. Can you please explain this part of the criteria?

EmilyH

Hi @ChelseaH, thanks for the reply.

The criteria is an either/or option.

If the Task 4 comment contains "all" (which represents all components are required), then display the Task 9 date (this is a later date for "all" components).

-or-

If the Task 4 comment does not contain the word "all" (not all components are required), then display Task 4 date (this is a date for fewer required components, not all).

Thank you.

Devin Lee

Hey @EmilyH,

See if you can modify this to get what you are looking for. You will have to replace the column references with cross-sheet references but it should work to pull in either the date from the matching task or if the word "all" is in the comments it will pull from Task 9. Not sure if Task 9 is always going to be the reference date but this is set up that way.

Standard formula:

=IF(CONTAINS("all", INDEX(Comment:Comment, MATCH(Task@row, [Task ID]:[Task ID], 0), 0)), INDEX(Date:Date, MATCH("Task 9", [Task ID]:[Task ID], 0), 0), INDEX(Date:Date, MATCH(Task@row, [Task ID]:[Task ID], 0), 0))

Replaced with References

=IF(CONTAINS("all", INDEX({Ref 1:Comment:Comment}, MATCH(Task@row, {Ref 2: [Task ID]:[Task ID]}, 0), 0)), INDEX({Ref 3: Date:Date}, MATCH("Task 9", {Ref 2: [Task ID]:[Task ID]}, 0), 0), INDEX({Ref 3: Date:Date}, MATCH(Task@row, {Ref 2: [Task ID]:[Task ID]}, 0), 0))

EmilyH

Hi @Devin Lee,

This is amazing...it worked perfectly!

Thank you so much!

Emily