Help

"OR" and "AND" search formula together

Topic Labels: Formulas
Solved
Jump to Solution
3861 7
cancel
Showing results for 
Search instead for 
Did you mean: 
rrbbtalnorth
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi, I have a database of professionals for whom I want to create a formula to search for the appropriate therapist using MAKE
There are categories where I want to search all searchable categories, and there are categories where I want to search one or another category.

for example-

I am interested in looking for a therapist “for children” in the area or “Tel Aviv” or Jerusalem.

AND(
SEARCH(“children”,{categorys})
OR(
SEARCH(“Tel Aviv”,{categorys})
SEARCH(“Jerusalem”,{categorys})
)
)

But apparently the formula is incorrect.
Would appreciate help
צילום מסך 2022-08-07 153654

1 Solution

Accepted Solutions
ScottWorld
18 - Pluto
18 - Pluto

One important thing to note is that Airtable is extremely finicky when it comes to capitalization. So Airtable will see “children” as different than “Children”.

See Solution in Thread

7 Replies 7

So all of the words you’re looking for are inside that one category formula field?

If so, this formula should work:

OR(

AND(
SEARCH("children",{categorys}),
SEARCH("Tel Aviv",{categorys})
),

AND(
SEARCH("children",{categorys}),
SEARCH("Jerusalem",{categorys})
)

)
ScottWorld
18 - Pluto
18 - Pluto

One important thing to note is that Airtable is extremely finicky when it comes to capitalization. So Airtable will see “children” as different than “Children”.

rrbbtalnorth
5 - Automation Enthusiast
5 - Automation Enthusiast

Thank you very much, but unfortunately the formula does not work

And I checked in capital letters, everything is the same

צילום מסך 2022-08-07 213109

צילום מסך 2022-08-07 213252

It is hard to read your screen capture but here are some possibilities.

  • Making sure you are using straight quotes " and not curly quotes. Your text example has some curly quotes “”.
  • Make sure you are not mixing up curly braces and parentheses: { ,(, } , ).
  • You are searching the field {Categorys} in all searches. Shouldn’t some of those searches be for the {Area} field or the {City} field?
  • Several of your fields are lookup fields. You should turn them into text strings in the search.

Try something like this:

AND(
    SEARCH("children", LOWER({categorys}) ),
    OR(
        SEARCH("Tel Aviv", {Area} & ""),
        SEARCH("Jerusalem", {Area} & "")
    )
)

Thank you very much, I will try

It works great. Thank you

Alternatively, note that it’s often easier to create & keep those large formulas in Airtable.

In Airtable, your formula field would result in a 1 (if it is true) or a 0 (if it is false).

Then, in Make.com, you would simply need to put the name of your formula field (within curly brackets) for your search criteria.