check column against another sheet of applicants

System Employee Admin
edited 04/22/24 in Formulas and Functions
This discussion was created from comments split from: Using an OR in COLLECT.


  • J Liguori
    J Liguori
    edited 04/19/24

    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
    Paul Newcome ✭✭✭✭✭✭

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

  • Andrée Starå
    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!


    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!


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

    W: | | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • 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
    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")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!