If, Index & Collect Formula issues

Office of the Medical Director

I have an issue with my current If, Index, and Collect formula.

I have two sheets. One for data collection, and another for the Reviewer's Agency, Schedule, and Email address. I want to automate this process where if I have a TFD C shift, the Reviewers email address will be added into the data collection sheet. Here's my formula:

=IF(Agency@row = "TFD", INDEX(COLLECT({Reviewers Emails}, {Reviewers Schedule}, Schedule@row), 1), IF(Agency@row = "NWFD", INDEX(COLLECT({Reviewers Emails}, {Reviewers Schedule}, Schedule@row), 1), IF(Agency@row = "GRFD", INDEX(COLLECT({Reviewers Emails}, {Reviewers Schedule}, Schedule@row), 1))))

So I have a list of Reviewers:

If TFD C schedule, then Phil is the reviewer

If TFD B schedule, then Joe is the reviewer

If NWFD A, B or C schedule, Rachel is the reviewer

If GRFD B schedule, Mary is the reviewer

If GRFD C schedule, Tyrel is the reviewer

With my current formula, only the TFD reviewers are working, but not any of the NWFD or GRFD. The NWFD and GRFD Reviewers are just blank. Below is a screen shot of my reference sheet:

What am I doing wrong? Any assistance would be greatly appreciated, and thank you in advance!

Thanks,

Adrienne

Paul Newcome

Try an INDEX/COLLECT instead.

=INDEX(COLLECT({Contact Column}, {Agency Column}, @cell = Agency@row, {Schedule Column}, @cell = Schedule@row), 1)

Paul Newcome

Which sheet is your screenshot of and can you provide a screenshot of the other sheet in question?

Office of the Medical Director

Here's a screen shot with the non-essential columns hidden. I was using the testing column to write out my formula, but I ultimately want the formula to be in the Medical Reviewer column. Currently the Medical Reviewer Column is a drop-down that we select and then mark "Ready to Review" when we want that row sent to the reviewer. I just want to automate the Reviewer's email into Medical Reviewer column based on the Agency and schedule listed per row. The first screen shot was the sheet listing the reviewers and their assigned agency and shift.

Office of the Medical Director

The 2nd screen shot is the data entry sheet where we want the formula to be written into the Medical Reviewer column

Paul Newcome

Try an INDEX/COLLECT instead.

=INDEX(COLLECT({Contact Column}, {Agency Column}, @cell = Agency@row, {Schedule Column}, @cell = Schedule@row), 1)

Office of the Medical Director

@Paul Newcome that worked!!! Thank you!!!!

Paul Newcome

Happy to help. 👍️