Matching a single drop down result among multiple using a formula (and mirror columns)

I’m trying to look for incompatibilities between potential housemates using a formula. I’ve gotten it working using status columns. But when I am trying to match using drop downs, it only works when there is just a single selection. If there are multiple, the “equals” operator returns FALSE, even if the desired match is present among the drop down selections.

I think I need an operator that’s more like “INCLUDES”. Or another approach?

More details below.

There are 4 relevant input columns:
“Lives with” (who a person lives with, like “Partner” or “Child(ren)”)
“Not with” (who a person won’t live with, like “Children” or “Couples”)
“Match lives with” (who a potenial match lives with, same options as “Lives with”)
“Match not with” (who a potential match won’t live with, same options as “Not with”)

My formula is a series of AND tests of incompability inside an OR inside an IF, which should return :no_entry: if any AND is true. Or that’s my aim. The outer IF is to make the formula cell blank rather than show an error in the event no person has been chosen (via a connect board connection) to try as a “Match”.

IF({Match}="", “”,
IF(
OR(
AND({Lives with}=“Child(ren)”, {Match not with}=“Children”),
AND({Match lives with}=“Child(ren)”, {Not with}=“Children”),
AND({Lives with}=“Partner”, {Match not with}=“Couples”),
AND({Match lives with}=“Partner”, {Not with}=“Couples”),
AND({Gender}=“Female”, {Match not with}=“Females”),
AND({Match Gender}=“Female”, {Not with}=“Females”),
AND({Gender}=“Male”, {Match not with}=“Males”),
AND({Match Gender}=“Male”, {Not with}=“Males”)
),“:no_entry:”,“:family:”)
)

It works as written if a person has e.g. “Not with” has “Females” or “Children” but not if they have both.

In case it’s relevant, I’m using mirrored status columns. These are flagged as not yet supported. And they do cause some buggy behavior, I believe. The formula works when I start a new match by connecting an item in a connect board. But if I update that item while connected, the formula doesn’t update. But I don’t think that’s affecting my problem with the “equals” operator not finding one among multiple items.

Happy to demonstrate on video if that helps.

I’ve tried using SEARCH.

However SEARCH appears to return the position of the first character in the first matching substring. And it’s not case sensitive.

E.g. SEARCH("Males",{Not with}) will return 3 when {Not with} contains “Females”.

Is there a way to force the SEARCH function to be case sensitive?

BTW, Monday’s reference page for the formula functions doesn’t include syntax? Just examples. The syntax is in formula menus. But without examples of the output?

@ivan.d

FIND() is basically a case-sensitive version of SEARCH().


Jim - The Monday Man (YouTube Channel)
What is Make & How can it help you?
We Create Custom Solutions

Thanks Jim!

I was embarrassed not to have found FIND, but it isn’t listed here: https://support.monday.com/hc/en-us/articles/360001276465-List-of-all-available-formulas

@ivan.d

It’s not there.

There are a number of undocumented functions that are available in monday’s formulas.

You can find our more about them here: The monday Hidden Functions – The Monday Man

That’s a great resource you’ve made!

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.