Check if a record is linked in two different fields

[christopherl]

I have to following situation :

How to create a formula that will return “True” or “1” if at least one record appears in both fields ?

[ScottWorld]

IF(
AND({Question 1},{Question 2}),
1)

[christopherl]

This does not seems to work, unfortunatly. It returns 1 when both fields are populated (Whatever the values), and 0 when either field is empty.

[christopherl]

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.

[ScottWorld]

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:

ARRAYUNIQUE(values)

And be sure to set the condition to only show you values when the formula field is 1.

[christopherl]

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.

[ScottWorld]

Oh, I see… those 2 lookup fields are coming from 2 completely different tables? Yeah, that would be more challenging to solve. Maybe someone else has a quick solution for that.

[christopherl]

They are coming from the same table, but from different records. The structure is :

• A participant table (all participants)
• An answer table (a list of answers)
• A match table (all combination of participants)

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.