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: https://docs.google.com/spreadsheets/d/1VBrkVwBa5jXcrQXwB7QGFJhgcbBfjcmCawV7B1YqPcI/edit?usp=sharing Would anyone be able to help please? Thanks in advance!
1 Answers
If you change the empty values inside the "Action Plan" tab to Yes. You can use the following formulas:
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:
I added a Data Validation on my copy to make it easier to change the location and the time.
Like this:
Reference:
Related