Cross Row Matching
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.
Best Answers

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.)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Thank you Jeff

@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!
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers

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.)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

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.

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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Thank you Jeff

@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!
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

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!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.9K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.6K Ideas & Feature Requests
 56 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!