Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

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

1105 0
cancel
Showing results for 
Search instead for 
Did you mean: 
David_Smedberg
6 - Interface Innovator
6 - Interface Innovator

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!

0 Replies 0