IF cell matches criteria in one column, THEN return other columns from matching row
Help, please! I am trying to search a MASTER sheet for ALL ROWS that match two criteria (MASTER AuthorPartner 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 AuthorPartner column is a multiselect contact list, and I need to pull in rows where the value in [Project1] appears anywhere in the MASTER AuthorPartner 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!
Best Answers

You could use a JOIN(COLLECT formula for this, which will "Join" together all the cells from one column that match the criteria you specify in the COLLECT function.
The way JOIN(COLLECT works is that you first list the range that has the value you want returned (ex. the Current Project Topic column) and then list each range and criteria afterwards. Then you add in the delimiter (what separates the different values... a comma? A dash?)
For example:
=JOIN(COLLECT({Current Project Topic}, {Category Column}, Project@row, {Author Column}, "Andrew Lastname"), " , ")
Now, we would actually want to adjust this because your contact is listed in a MultiSelect contact column. This means we'll want to use HAS to see if that column HAS that user in a cell, whether or not he is with other users. Try this:
Final Formula:
=JOIN(COLLECT({Current Project Topic}, {Category Column}, Project@row, {Author Column}, HAS(@cell, "Andrew Lastname")), " , ")
The ranges in {these} are crosssheet references to columns in your first sheet. The values in [these] represent the column in sheet 2, where the formula is being written. To adjust it for each column (Product, status, etc), just change what the first column is after the COLLECT function.
You can read more about each of these functions in our Help Center: JOIN function / COLLECT function / @row function / Cross Sheet References
Let me know if this works for you, or if you have any questions about the above formula!
Cheers,
Genevieve

@Genevieve P, you not only solved my problem, you helped me to understand the formula I was using (sort of  it's crazy that collect from X if it meets Y and Z conditions are all just separated by commas, but at least I now understand and can replicate this). Thank you so much!
I modified the HAS portion so that it would find whatever is dynamically showing up in cell Project1, and I changed the delimiter to "  ". Thus my final formula was:
=JOIN(COLLECT({Current Project Topic}, {Project Category}, Project@row, {Current Author/Partner}, HAS(@cell, Project1)), "  ")
Success! 🙌
Answers

You could use a JOIN(COLLECT formula for this, which will "Join" together all the cells from one column that match the criteria you specify in the COLLECT function.
The way JOIN(COLLECT works is that you first list the range that has the value you want returned (ex. the Current Project Topic column) and then list each range and criteria afterwards. Then you add in the delimiter (what separates the different values... a comma? A dash?)
For example:
=JOIN(COLLECT({Current Project Topic}, {Category Column}, Project@row, {Author Column}, "Andrew Lastname"), " , ")
Now, we would actually want to adjust this because your contact is listed in a MultiSelect contact column. This means we'll want to use HAS to see if that column HAS that user in a cell, whether or not he is with other users. Try this:
Final Formula:
=JOIN(COLLECT({Current Project Topic}, {Category Column}, Project@row, {Author Column}, HAS(@cell, "Andrew Lastname")), " , ")
The ranges in {these} are crosssheet references to columns in your first sheet. The values in [these] represent the column in sheet 2, where the formula is being written. To adjust it for each column (Product, status, etc), just change what the first column is after the COLLECT function.
You can read more about each of these functions in our Help Center: JOIN function / COLLECT function / @row function / Cross Sheet References
Let me know if this works for you, or if you have any questions about the above formula!
Cheers,
Genevieve

@Genevieve P, you not only solved my problem, you helped me to understand the formula I was using (sort of  it's crazy that collect from X if it meets Y and Z conditions are all just separated by commas, but at least I now understand and can replicate this). Thank you so much!
I modified the HAS portion so that it would find whatever is dynamically showing up in cell Project1, and I changed the delimiter to "  ". Thus my final formula was:
=JOIN(COLLECT({Current Project Topic}, {Project Category}, Project@row, {Current Author/Partner}, HAS(@cell, Project1)), "  ")
Success! 🙌

@Michelle Schwartz  I'm so glad to hear this worked for you, and that you understand it! Your final formula looks great... I really like the two lines for the formatting. 🙂
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!