IF cell matches criteria in one column, THEN return other columns from matching row

Michelle Schwartz

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!

Genevieve P.

Hi @Michelle Schwartz

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 Multi-Select 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 cross-sheet 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

Michelle Schwartz

@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! 🙌

Genevieve P.

Hi @Michelle Schwartz

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 Multi-Select 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 cross-sheet 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

Michelle Schwartz

@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! 🙌

Genevieve P.

@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. 🙂