Formula to match two column inputs within one row in two separate Smartsheet's.

Roster Smartsheet:

Course Email Eval Completed

Class1 [email protected] =formula here

Class1 [email protected] =formula here

Class2 [email protected] =formula here

Evaluation Smartsheet:

Course Email

Class1 [email protected]

Class1 [email protected]

Class2 [email protected]

Formula that doesn't work:

=IF(AND(RosterSS[Course]=EvaluationSS[Course],RosterSS[Email]=EvaluationSS[Email]),"yes","no")

QUESTION:

How can I write the formula to look up the course column and email column in two different Smartsheets and match them to answer Yes or No on whether each email (student) has completed a course eval for the class they attended.

Best Answer

  • Leibel Shuchat
    Leibel Shuchat ✭✭✭✭✭
    Answer ✓

    @CNC

    You can use COUNTIFS to check if there is a row in the evaluation sheet with the class and email matching.

    See below formula, change the cross sheet reference (Bolded) to match yours...

    =IF(COUNTIFS({Evaluation Sheet - Course Column},@cell= [Course]@row,{Evaluation Sheet - Email Column},@cell=[Email]@row)>0,"Yes","No")

Answers

  • Leibel Shuchat
    Leibel Shuchat ✭✭✭✭✭
    Answer ✓

    @CNC

    You can use COUNTIFS to check if there is a row in the evaluation sheet with the class and email matching.

    See below formula, change the cross sheet reference (Bolded) to match yours...

    =IF(COUNTIFS({Evaluation Sheet - Course Column},@cell= [Course]@row,{Evaluation Sheet - Email Column},@cell=[Email]@row)>0,"Yes","No")

  • @Leibel Shuchat - Thank you for the correct formula! This works great.😃