Search code examples
google-sheetssplitgoogle-sheets-formulaflattengoogle-query-language

Query sheet and return Column Headers that match two criteria in a row

I am trying to return an array of column headers based on TRUE values and Item names.

Link to sheet <- Clicky

Sheet 2:

enter image description here

Sheet 1: (desired results)

enter image description here

I think I am close but can't rack my brain to search for both TRUE values within the corresponding Item and return the headers for each. I have tried the following but it returns nothing. It is as far as I have gotten.

=IFERROR(QUERY(QUERY(Sheet2!A1:D,"Select * where G='"&A1&"'",1),"Select Col1 where Col2 is not null"))
Solution

  • use:

    =INDEX(IFERROR(SUBSTITUTE(SPLIT(TRIM(FLATTEN(QUERY(TRANSPOSE(
 IF(Sheet2!A2:D=TRUE, SUBSTITUTE(Sheet2!A1:D1, " ", "♦"), )),,9^9))), " "), "♦", " "))

    enter image description here

    update:

    =INDEX(IFNA(VLOOKUP(A1:A, {Sheet2!G2:G, 
 IFERROR(SUBSTITUTE(SPLIT(TRIM(FLATTEN(QUERY(TRANSPOSE(
 IF(Sheet2!A2:D=TRUE, SUBSTITUTE(Sheet2!A1:D1, " ", "♦"), )),,9^9))), " "), "♦", " "))}, 
 {2,3,4,5}, 0)))