Return Value from Another Sheet based on Two Criteria

Bethany Garcia

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

Paul Newcome

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

Paul Newcome

Try a COUNTIFS. Count how many rows have both. If that number is greater than zero, then at least one row matches and you can output "Yes".

=IF(COUNTIFS(..............)> 0, "Yes", "No")

Bethany Garcia

@Paul Newcome I follow what you're saying but I don't think I did it correctly. I think I'm missing something to indicate that the criteria needs to apply to the same row meeting both criteria.

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

Paul Newcome

That's exactly it. The COUNTIFS will only generate a number/count if the email and the "Civility In the Workplace" are both on the same row.

Bethany Garcia

@Paul Newcome the formula I pasted above isn't working though. When I use that formula it's returning a Yes for employees who don't have that training type in the sheet. One thing that may be a factor is that the training and employee columns on the training tracking sheet are multi-select, so there may be more than one value in that column. Do I need to adjust the logic to look for contains the employee and training? Even then though, it's returning a Yes for an employee row when that employee doesn't have that specific training in their row, but that training does exist in the column.

Paul Newcome

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

Bethany Garcia

@Paul Newcome that worked, thank you so much!

Paul Newcome

Happy to help. 👍️