What is the most efficient way to cross link contact lists?

jking108

I have a couple of converging problems that utilize common terms to facilitate automatic notification when employees call out sick.

My current workflow is that users submit a form with their sick call information (normally location + department). Department + location are combined in the notification log to facilitate a vlookup (below) to collect contact information from a separate sheet. This ultimately triggers an email notification OR or a manually triggered automation when it identifies an error (i.e. combination of department + location doesn't exist). Currently, I have roughly 410 individual departments.

=IFERROR(VLOOKUP([Sick Call Distro Email]@row, {Distribution lists Range 2}, 2, false), "Send Manual Notification Email")

I'm using variations of the same formula that return a different column number (2, 3, or 4) to avoid the limit of 20 individuals in a contact list.

The current process of keeping contact lists updated is manual copy and paste and I need to transition to fully automated maintenance. I've leveraged an automation and a form to collect and approve contact lists. I'm seeking a method to pull the most recent approved contact list for any department+location@row combination from the intake sheet to the distribution list directory. Is a variation of max/collect the best way to pull values from the intake sheet (using most recent modification + Department + Location) to populate the three contact columns? If so, how would I structure the formulas to pull information from selected locations within the matrix?

Paul Newcome

Are you able to provide screenshots with sample data for context?