Index/collect/join-match formula when source sheet row meets multiple criteria



I'd like to apologise beforehand, but my brain is fried at this point. I do believe it's something easy and I did go through similar entries on this forum prior to posting this.

I'd like to have a cross-sheet formula where, once certain criteria of cell values are met in the source sheet, it will update my date. That is - if the Study # matches, and the MDR Output in the source sheet is value "ABC", I want it to update my Start Date cell in my other sheet.

I've tried index-match, index collect, join collect, but to no avail.

There are multiple entries of the same project code in the source sheet and the way I want to cross- sheet is, is that it will update the date only when the Study # when the "MDR output" has a certain value (a text/number field).

=JOIN(COLLECT({Start Date}, {MDR Output}@cell="ABC"), MATCH([Study #]@row, {study #}), 0)

Can someone please help? I'm either getting #UNPARSEABLE or #INCORRECT ARGUMENT SET.


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!