If True result based on a query output of 2 results

0
votes

In column F I have the below formula which references the status of sheet 2 column G. The query formula produces 2 results populating cells F2 & F3. I want an "if" formula that if my query produces 2 cells containing yes, yes then say true but if it produces yes, no then produce false or no, no produce false.

This is the link to the sheet for reference. https://docs.google.com/spreadsheets/d/1C5xWlw9vMZMNhXprSCBZsNp0T9qMxvuooIP8I6JoI2Y/edit#gid=0

enter image description here

2
if-statementgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-query-language

2 Answers

1
votes

delete F2:F range and paste this in F2 cell:

=ARRAYFORMULA(IFNA(VLOOKUP(A2:A&E2:E, QUERY({Sheet2!A2:A&Sheet2!B2:B, 
 IF(Sheet2!G2:G="yes", 1, 0)}, 
 "select Col1,sum(Col2) 
  where Col1 is not null 
  group by Col1 
  label sum(Col2)''"), 2, 0)=2))

0

if Sheet2 jobs are not always in pairs use:

=ARRAYFORMULA(IFNA(VLOOKUP(A2:A&E2:E, QUERY({Sheet2!A2:A&Sheet2!B2:B, 
 IF(Sheet2!G2:G="yes", 1, 0)}, 
 "select Col1,sum(Col2) 
  where Col1 is not null 
  group by Col1 
  label sum(Col2)''"), 2, 0)=
 IFNA(VLOOKUP(A2:A&E2:E, QUERY({Sheet2!A2:A&Sheet2!B2:B, 
 IF(Sheet2!G2:G="yes", 1, 0)}, 
 "select Col1,count(Col2) 
  where Col1 is not null 
  group by Col1 
  label count(Col2)''"), 2, 0))))
0
votes

I was able to come up with an answer with collaboration of some awesome people. The way I solved it, for now, is changing the where clause to focus on the "False" response only and wrapping it in an if and iferror to give me the True response

=iferror(ifna(if(query(IMPORTRANGE("https:/...","range"),"select Col8 where Col1 = '"&A7&"' and '"&E7&"' = Col2 and Col8 = 'No'",0)="No",FALSE,TRUE),TRUE),FALSE)