Hi all, just thought I’d share an insight I had about working with lists.
Recently, I had a project where we needed to take an email address from a customer-submitted form and check whether the email is from a list of known domains.
I could have used an OR() statement, but that would have created a very lengthy formula, because I had to first separate out the domain from the rest of the email, using this formula:
LOWER(MID(TRIM({Email}),FIND("@",TRIM({Email}))+1,999))
Thus, if I have to compare that to 5 different domains using an OR() statement, I’d have to repeat that formula snippet 5 times, and that’s not very DRY, is it?
What I realized was that instead, I could use a SWITCH() statement. Since a SWITCH() statement allows you to optionally include a final parameter in case none of the cases listed matches the initial text, you can use it as a sort of hack to go through long lists (similar to how, in Excel, you could use a MATCH() statement). This still has a drawback, in that each item in the list that you’re searching for has to be listed twice, but it’s closer to the ideal than the OR() statement alternative:
IF(SWITCH(LOWER(MID(TRIM({Email}),FIND("@",TRIM({Email}))+1,999)), "domain1.com", "domain1.com", "domain2.com", "domain2.com", "domain3.com", "domain3.com", "domain4.com", "domain4.com", "domain5.com", "domain5.com", "@")="@", "Email address is from unknown domain")
Anyway, this turned out to be helpful for me, so I hope it helped you too!
