2

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"))
player0
  • 124,011
  • 12
  • 67
  • 124
Kevin P.
  • 907
  • 7
  • 18

2 Answers2

1

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)))
player0
  • 124,011
  • 12
  • 67
  • 124
  • Although I was hoping my query approach was close because I can understand it much easier this is flawless. Thank you! – Kevin P. Jan 12 '22 at 21:28
  • Oh, I apologize I just realized it does not take into consideration Column A listings, I need it to depend on what Column A says and match that string item for item. This detail was not considered when I made the post because my thought process and trial formula was already in that mindspace. – Kevin P. Jan 12 '22 at 21:34
  • @KevinP. answer updated – player0 Jan 12 '22 at 21:37
1

If anyone is interested in another way, I did find a solution utilizing a preferred method of sticking with minimal functions that I have a better understanding of, I was simply missing the transpose function.

=TRANSPOSE(QUERY(TRANSPOSE(QUERY(Sheet2!$A$1:$G,"Select * where G = '"&$A1&"'",1)),"Select Col1 where Col2 = TRUE"))

The only unfortunate part is I cannot seem to get it to expand down but it will return the results horizontally in a cell. It is also MUCH faster.

To get the results separated by comma's in a single row you could use JOIN:

=JOIN(", ",TRANSPOSE(QUERY(TRANSPOSE(QUERY(Sheet2!$A$1:$G,"Select * where G = '"&$A1&"'",1)),"Select Col1 where Col2 = TRUE")))
Kevin P.
  • 907
  • 7
  • 18