Cross Row Matching

Stepheni

I need a solution to match roommates.

When they sign up via form, each person enters the email address of their preferred roommate.

1) For each volunteer (Stepheni), I need to look at the preferred roommate (Mike) email, and see if that person has signed up yet. I have this with a vlookup.

2) If the preferred roommate (Mike) has signed up, I need to look at Mikes preferred roommate to see if it is Stepheni.

This is where I am stuck. I am at a loss on how to formula this check and confirmation.

3) if the preferred roommates align, they are confirmed roommates.

Jeff Reisman

Checkbox Columns: Roommate Signed Up, and Roommate Confirmed

Roommate Signed Up column:

=IF(HAS(Email:Email, [Preferred Roommates(s)]@row), 1, 0)

(If the Email column contains the Preferred roommate from this row, check the box.)

Roommate Confirmed column:

=IF(AND([Roommate Signed Up]@row = 1, (INDEX([Preferred Roommates(s)]:[Preferred Roommates(s)], MATCH([Preferred Roommates(s)]@row, Email:Email, 0)) = Email@row)), 1, 0)

(If the Roommate Signed Up box on this row is checked, and the Preferred Roommate of the Preferred roommate from this row equals the email from this row, check the box.)