IF AND CONTAINS Formula with Multiple Criteria in Another Sheet


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


    J Tech

    If my response has helped you in any way or provided a solution to your problem, please consider supporting the community by marking it as Insightful, Vote Up, or Awesome. Additionally, you can mark it as the accepted answer, which will make it easier for others to find a solution or get help with a similar issue in the future. Your support is greatly appreciated!
  • 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?


  • Paul Newcome
    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, "-----")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!