Help, please! I am trying to search a MASTER sheet for ALL ROWS that match two criteria (MASTER Author-Partner matches the value in [Project1], and MASTER Project category matches Project@row), and then return the contents of certain cells within each matching row (Title/Topic, Product type, Status, Due Date).
I have had limited success with the following two formulas, which do return a Title/Topic. These formulas were both set up by our awesome Smartsheet consultant (so I don't deeply understand them). The problem is that they only return the FIRST ROW result, regardless of whether the Project field is a match.
=INDEX({Current Project Topic}, MATCH(Author#, {Current Author/Partner}, 0))
=IFERROR(INDEX(COLLECT({Current Project Topic}, {Current Author/Partner}, Author#), MATCH(Project@row, {Project Category}, 0)), "")
Possible complicating factors include (1) the value in [Project1] is generated by a formula; (2) the MASTER Author-Partner column is a multi-select contact list, and I need to pull in rows where the value in [Project1] appears anywhere in the MASTER Author-Partner column, whether as a single author or one of many; (3) in some cases, it's possible an author will have more than one product within a project category (such as a Transformations essay and a Transformations video, or two essays in the Apocalypticism category).
MASTER
DYNAMIC
Thanks in advance for your help!