Home > Back-end >  IF(AND) formula where multiple criteria need to be a match is not working
IF(AND) formula where multiple criteria need to be a match is not working

Time:07-11

I am trying to get a TRUE/FALSE value when 4 criteria match. On the Sheet3 tab, I would like to have either TRUE or FALSE under col C if the following are a match: Sheet3!F2 = ActionPlan!B2:B6; Sheet3!F1 = ActionPlan!A2:A6; Sheet3!B2 = ActionPlan!D1:H1; and if there is a "No" in the data range ActionPlan!D2:H6 Please see the example sheet here: enter image description here

Note: I added a message in case the Time & date and the location do not match with the information in the "Action plan" tab

Question 1:

=IFNA(IF(FILTER('Action plan'!$D$2:$D,'Action plan'!$A$2:$A=$F$1,'Action plan'!$B$2:$B=$F$2)="Yes","True","False"),"Time and location need to match")

Question 2:

=IFNA(IF(FILTER('Action plan'!$E$2:$E,'Action plan'!$A$2:$A=$F$1,'Action plan'!$B$2:$B=$F$2)="Yes","True","False"),"Time and location need to match")

Question 3:

=IFNA(IF(FILTER('Action plan'!$F$2:$F,'Action plan'!$A$2:$A=$F$1,'Action plan'!$B$2:$B=$F$2)="Yes","True","False"),"Time and location need to match")

Question 4:

=IFNA(IF(FILTER('Action plan'!$G$2:$G,'Action plan'!$A$2:$A=$F$1,'Action plan'!$B$2:$B=$F$2)="Yes","True","False"),"Time and location need to match")

Question 5:

=IFNA(IF(FILTER('Action plan'!$H$2:$H,'Action plan'!$A$2:$A=$F$1,'Action plan'!$B$2:$B=$F$2)="Yes","True","False"),"Time and location need to match")

The result will be: enter image description here

I added a Data Validation on my copy to make it easier to change the location and the time.

Like this:

enter image description here

Reference:

  • Related