IF AND CONTAINS Formula with Multiple Criteria in Another Sheet

Karen_PM

I'm trying to pull data that meets multiple criteria from a master schedule into a separate individual schedule. If the data meets the 3 criteria, the cell populate with a value from another cell. I've tried many different formulas but haven't had any luck. The image on the left is the master schedule and the image on the right is the individual schedule.

This is the formula I'm trying to use:

=IF(AND({Convention Assignments v2 Time of Event} = Time@row, {Convention Assignments Sample Range 3} = Day@row, CONTAINS("Angela Smith", {Convention Assignments Sample Range 1})), Registration1, "-----")

but I'm getting "Invalid Operation". I've tried several different iterations without success. I did have success with this formula using dummy data within the individual sheet. Any assistance would be very much appreciated.

J Tech

Hi @Karen_PM,

Based on the formula you provided, it looks like there might be an issue with the CONTAINS function. You need to provide the range that the function should look for the text "Angela Smith". Here's an updated formula that should work:

=IF(AND({Convention Assignments v2 Time of Event} = Time@row, {Convention Assignments Sample Range 3} = Day@row, CONTAINS({Convention Assignments Sample Range 1}, "Angela Smith")), Registration1, "-----")

Karen_PM

Hi @J Tech,

Thank you for your response. I tried the updated formula and I'm still receiving an "invalid operation" error. Any additional suggestions?

Karen

Paul Newcome

Try a COUNTIFS instead.

=IF(COUNTIFS({Convention Assignments v2 Time of Event}, @cell = Time@row, {Convention Assignments Sample Range 3}, @cell = Day@row, {Convention Assignments Sample Range 1}, CONTAINS("Angela Smith", @cell))> 0, Registration1, "-----")