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

Michaela Kamenska

Hello!

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.

Paul Newcome

Try something like this...

=INDEX(COLLECT({Start Date}, {MDR Output}, @cell = "ABC", {Study #}, [Study #]@row), 1)

Paul Newcome

Try something like this...

=INDEX(COLLECT({Start Date}, {MDR Output}, @cell = "ABC", {Study #}, [Study #]@row), 1)

Michaela Kamenska

Hi @Paul Newcome,

Thanks so much, you're always such a great help!

As mentioned in my original post, I assumed it was going to be easy, I just somehow couldn't get it going myself.

Thanks!!!

Paul Newcome

Happy to help. 👍️