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.)

Jeff Reisman

One hiccup, if no match, I get the #no match error. Can that just be blank like the previous column?

With any formula that could generate an error like this, after you're sure it's working as expected, you can wrap it in IFERROR in order to replace the error message with something else:

=IFERROR(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), 0)

IFERROR goes in front, and at the end you put your replacement value. Usually this is ,"") to produce a blank, but it could be any alternate value you want, even another formula. In this case, I used 0 at the end since it's a checkbox column, but "" would have worked too.

Stepheni Norton

Thank you Jeff

Jeff Reisman

@Stepheni Norton Happy to help, and glad it worked on the first try, because I didn't even test it 😊

If you could mark my first answer as Accepted, that would be great!

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.)

Stepheni Norton

Thank you Jeff, that worked great.

One hiccup, if no match, I get the #no match error. Can that just be blank like the previous column?

I really appreciate your help.

Jeff Reisman

One hiccup, if no match, I get the #no match error. Can that just be blank like the previous column?

With any formula that could generate an error like this, after you're sure it's working as expected, you can wrap it in IFERROR in order to replace the error message with something else:

=IFERROR(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), 0)

IFERROR goes in front, and at the end you put your replacement value. Usually this is ,"") to produce a blank, but it could be any alternate value you want, even another formula. In this case, I used 0 at the end since it's a checkbox column, but "" would have worked too.

Stepheni Norton

Thank you Jeff

Jeff Reisman

@Stepheni Norton Happy to help, and glad it worked on the first try, because I didn't even test it 😊

If you could mark my first answer as Accepted, that would be great!

Stepheni

Hi Jeff, I have another question for you related to this.

Now that I can match the roommates - thank you again 😊, I'd like to assign the pair a confirmation code that is the same for each of the roommates, but uniquely to the group.

For example Michael and Stepheni matched, so they would have the same code in the ConfirmCode column. It doesn't have to be a number just unique to the list and matched to the pair.

Thank you for your help!