How to use SWITCH statements to find a match in a list

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!