Skip to main content
Solved

FilterByFormula - Exact match for Multi-Value Columns

S
Forum|alt.badge.img+3

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?

Best answer by Milan_Automable

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

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

View original
Did this topic help you find an answer to your question?

5 replies

ScottWorld
Forum|alt.badge.img+33
  • ScottWorld
  • Brainy
  • 8794 replies
  • February 19, 2025

@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
Forum|alt.badge.img+8
  • Milan_Automable
  • Participating Frequently
  • 47 replies
  • Answer
  • February 19, 2025

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

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

S
1 person likes this
  • S
    simentengs
S
Forum|alt.badge.img+3
  • simentengs
  • Author
  • New Participant
  • 2 replies
  • February 19, 2025
ScottWorld wrote:

@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


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.

S
Forum|alt.badge.img+3
  • simentengs
  • Author
  • New Participant
  • 2 replies
  • February 20, 2025
Milan_Automable wrote:

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

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


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

Milan_Automable
Forum|alt.badge.img+8
  • Milan_Automable
  • Participating Frequently
  • 47 replies
  • February 20, 2025

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

 

S
1 person likes this
  • S
    simentengs

Reply


Most helpful members this week

Terms & Conditions