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
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!