Question on using IF, MAX, Collect, HAS to return criteria from multiple columns

jroob

Hello,

I have a form to submit certifications where the user enters their email address and a date of completion. Then in another sheet, I have employee data that includes a primary email and a secondary email.

I have a column in the 2nd sheet that uses max and collect to pull the date of the certification completion using references from the form that looks like this

=MAX(COLLECT({Completion Date}, {Email}, [Primary Email]@row, {Training}, HAS(@cell, "Training Name")))

My issue though is that some users in the sheet have a primary email, some are alternate emails, and so the form has a mix of submissions where some users submit with their primary and others using their alt email.

I'd like the ability to have some type or OR function, where it checks to verify the email in the PRIMARY EMAIL first and if that is not a match then try to do the same thing using the [Alternate Email]@row instead.

Any suggestions?

Victoria_Indimar

Hello! Not sure I fully understand - can you show an example of your sheet(s) setup?

Hollie Green

Try the below it should bring in the Max value between the two collect functions. I did a similar formula that worked for me.

=MAX(COLLECT({Completion Date}, {Email}, [Primary Email]@row, {Training}, HAS(@cell, "Training Name"),COLLECT({Completion Date}, {Email}, [Alternate Email]@row, {Training}, HAS(@cell, "Training Name"))