check column against another sheet of applicants

System

This discussion was created from comments split from: Using an OR in COLLECT.

J Liguori

Hi @Paul Newcome and @Andrée Starå ! I have a variation on this topic that I can't riddle. I have a sheet of leads which has a singular column for email addresses. I need to check this column against another sheet of applicants which has two columns of email ({UserEmail} and {NonXXemail}), in order to understand if a lead submitted an application. I can't figure out the syntax and where to put OR.

I've done several variations of this, but it just brings back 'Not found'.

=IFERROR(INDEX(COLLECT({AppStatus}, OR({UserEmail}, {NonXXemail}), [School Email Address]@row), 1), "Not found")

Any thoughts? Thanks in advance!

Paul Newcome

@J Liguori Are you able to provide some screenshots for context? Sample data is fine if needed.

Andrée Starå

Hi @J Liguori

I hope you're well and safe!

Yes, I agree with Paul.

Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

J Liguori

Thanks @Paul Newcome and @Andrée Starå! It's really kind of you to respond so quickly.

So sheet 1 gets populated by leads using a form. They are requested to put in an email address from their university. I want Sheet 1 to collect the Status, Program name, Application cycle, and Date app created from Sheet 2. Sheet 2 is pulled from an application management system. I am trying to answer the question "Did any student who submitted a form wind up applying for a program?"

=IFERROR(INDEX(COLLECT({AppStatus}, {UserEmail}, [School Email Address]@row), 1), "Not found")

To field for as many email address variations as possible, I pull two different emails from the application management system. To increase the likelihood of a match, I'd like for my formula to check both 'User email' and Non-XX email address'.

I hope these screenshots are helpful. Thanks again!

Paul Newcome

You could write a second INDEX/COLLECT to evaluate on the {Non-xx email Address} range and drop that into the second portion of the first IFERROR.

=IFERROR(INDEX(COLLECT(first column to search through), 1), INDEX(COLLECT(second column to look through), 1))

Then wrap that whole thing in another IFERROR to output your "Not Found" in instances where it is not found in either.

=IFERROR(IFERROR(INDEX(COLLECT(first column to search through), 1), INDEX(COLLECT(second column to look through), 1)), "Not Found")