Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

FilterByFormula - Exact match for Multi-Value Columns

Topic Labels: Formulas
Solved
Jump to Solution
223 5
cancel
Showing results for 
Search instead for 
Did you mean: 
simentengs
4 - Data Explorer
4 - Data Explorer

Hello Airtable Community!

I'm facing a challenge with constructing a filterByFormula and came across a similar issue discussed here: https://community.airtable.com/t5/formulas/formula-with-multiple-values/td-p/174768.

I have a column named Klasse, which can contain multiple values. I want to check if my search terms ('A', 'A1', or 'A2') match any of the entries in the Klasse column. Using the method suggested in the linked discussion, I crafted the following formula:

 

 
OR(FIND('A', {Klasse}) > 0, FIND('A1', {Klasse}) > 0, FIND('A2', {Klasse}) > 0)

This formula is intended to verify if 'A', 'A1', or 'A2' exists in any of the Klasse entries. However, I encounter an issue where FIND('A', {Klasse}) incorrectly matches entries containing 'A' as part of a longer string (e.g., 'AM146'). I am looking for a way to ensure that only exact matches are identified.

Could you provide some guidance on how to refine this formula to achieve exact matches only?

1 Solution

Accepted Solutions
Milan_Automable
6 - Interface Innovator
6 - Interface Innovator

FIND treats your multi-select as a string, eg. "A, A1, A2". See in the "Calculation" field below.

FindMultiSelect.gif

With regex, we can account for all 4 cases (A is the only one, is in the beginning, is in the middle, is at the end):

 

OR(
  REGEX_MATCH(Klasse, "^A,"),
  REGEX_MATCH(Klasse, "^A$"),
  REGEX_MATCH(Klasse, ", A$"),
  REGEX_MATCH(Klasse, ", A,")
)

 

This way, the formula will only match if "A" is among the values, and will still match if A1, A2 etc. is also among the values.

Hope this was helpful 🙂

I'd love to explore further use-cases for Airtable and automation in your business, feel free to book a call with me if you're interested!

-- Best, Milan - Automable.AI

See Solution in Thread

5 Replies 5

@simentengs 

You can use an AND() statement as one of your 3 OR() statements like this:

OR(
AND
(FIND('A', {Klasse}) > 0, FIND('A1', {Klasse}) = 0, FIND('A2', {Klasse}) = 0),
FIND('A1', {Klasse}) > 0,
FIND('A2', {Klasse}) > 0
)

Hope this helps! If you’d like to hire the best Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

Milan_Automable
6 - Interface Innovator
6 - Interface Innovator

FIND treats your multi-select as a string, eg. "A, A1, A2". See in the "Calculation" field below.

FindMultiSelect.gif

With regex, we can account for all 4 cases (A is the only one, is in the beginning, is in the middle, is at the end):

 

OR(
  REGEX_MATCH(Klasse, "^A,"),
  REGEX_MATCH(Klasse, "^A$"),
  REGEX_MATCH(Klasse, ", A$"),
  REGEX_MATCH(Klasse, ", A,")
)

 

This way, the formula will only match if "A" is among the values, and will still match if A1, A2 etc. is also among the values.

Hope this was helpful 🙂

I'd love to explore further use-cases for Airtable and automation in your business, feel free to book a call with me if you're interested!

-- Best, Milan - Automable.AI

Thank you for your suggestion! However, I think there's been a slight misunderstanding. When i search for OR(A, A1, A2) with the find() method, I wrongly receive rows where none of those entries are present. For example, I receive rows that only contains a Klasse entry "AM146", because the string 'A' is found in AM146. 

I've now tried to adopt a delimiter-based approach in my formula to ensure precise matching, to avoid getting only partial matches.

Here's the refined formula I'm trying:

OR( FIND('|A|', '|' & ARRAYJOIN({Klasse}, '|') & '|') > 0, FIND('|A1|', '|' & ARRAYJOIN({Klasse}, '|') & '|') > 0, FIND('|A2|', '|' & ARRAYJOIN({Klasse}, '|') & '|') > 0 )

This approach wraps each entry in the 'Klasse' column with pipes ('|') to form distinct boundaries, ensuring that 'A' is not confused with 'AM146',

It seems to work fine for rows where there is only one entry in the Klasse array, but when theres more it doesnt doesnt seem to return anything. 

I've attached a screenshot of the Klasse column to illustrate the Klasse setup better.

Screenshot 2025-02-19 at 13.41.51.png

This seems to be exactly what I'm looking for. Thanks! 🤝😄

Milan_Automable
6 - Interface Innovator
6 - Interface Innovator

Happy to help @simentengs 🙂

Also, I just realized this can be simplified to a single regular expression:

 

REGEX_MATCH(Klasse, "(^A$|^A,|, A$|, A,)")

 

(The "|" means "OR" within the expression)

Which is perhaps more straightforward to work with, especially if you want to check for multiple possibilities in the same expression, so you want another nested OR outside of it.

-- Best, Milan - Automable.AI Free Airtable consultation