Return Value from Another Sheet based on Two Criteria

Options

I have two sheets, Sheet A and Sheet B. Sheet A contains a list of submissions of completion of training, and the submissions can have multiple values in the Training column and Employee column (to indicate if more than one employee completed the training that day, and they could have completed more than one training that day).

Sheet B has a list of all employees in the first column, single value, with the names of each training as the headers for the remaining columns. I'm trying to create a formula to take the employee name from Sheet B (dynamic) along with the name of the training for that column (static) to scan Sheet A to see if there is a row that contains that employee's name on Sheet A in the Employee column, and also contains the training name in the Training Column.

I just want to return a simple Yes or No if a match is found.

I thought I was close with the below but I don't think it's looking for both criteria on the same row, just whether or not the employee name and training name exist on Sheet B.

=IF(AND(CONTAINS([Employee Name - Email]@row, {Working Well with Everyone Training Comple Range 1}), CONTAINS("What is Culture?", {Working Well with Everyone Training Comple Range 3})), "Yes", "No")

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    Ah. Sorry. I missed the multi-select piece. In that case yes. You will need to use a CONTAINS or HAS function. If they are true multi-selects, then I would suggest the HAS.

    =IF(COUNTIFS({Working Well with Everyone Training Comple Range 1}, HAS(@cell, [Employee Name - Email]@row), {Working Well with Everyone Training Comple Range 3}, HAS(@cell, "Civility In the Workplace")) > 0, "Yes", "No")

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!