check column against another sheet of applicants
Answers
-
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!
-
@J Liguori Are you able to provide some screenshots for context? Sample data is fine if needed.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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!
-
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")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!