I realize my orginal question was unclear ! I would need the formula to check if the same record appears in both columns, not if any record appears in both columns.
Told differently, those are questions for a form. I need to check if any answer given to question 1 was the same as the answer to question 2.
So if question 1 has [1,2,3] and question 2 has [2,5,6] it should return true.
To solve that problem, create that formula in the OTHER table instead of the CURRENT table.
Then, back in the CURRENT table, create a rollup field that points to the formula field with this formula:
And be sure to set the condition to only show you values when the formula field is 1.
Unfortunatly, I can’t do that, Q1 and Q2 are linked to two different records (ie, i’m trying to check if Participant 1 question 1 has any common answer with participant 2 Question 2).
I’ve been trying to jerryrig something with ARRAYUNIQUE and ARRAYJOIN, but that won’t work. I’m going to write a script, sadly, as this doesn’t feel like an edge case.
They are coming from the same table, but from different records. The structure is :
On the participant table, I have the fields “Question 1” and “Question 2” that are linked records to the “Answers” table.
On the match table, I have a record for every possible combination (Participant 1 with Participant 2, participant 1 with participant 3, participant 2 with participant 3, etc), with the lookups you see above.