I am looking to build a formula that will check columns for a true value and if true return the first column name, in order...

Below is the formula I have been trying to build but it is not working as expected although I think I am close.

=ARRAYFORMULA(IF(HLOOKUP($A16,{Sheet1!$B$2:$E$5,Sheet2!$B$8:$E$11},{3,4,5},0)=TRUE,{Sheet2!$A$3:$A$5,Sheet1!$A$3:$A5,""))

Let me illustrate. Sheet 1 and Sheet 2 contain names with who has what (checkbox items). With a formula in cell B16 that will populate both to the right and down with all the names that contain a TRUE value in the looked up value (A:A).

Above image was manually typed in, the formula does not work at all.

Please help!

EDIT:

demo sheet

CodePudding user response：

Here is another approach (similar to player0's, but different enough to warrant the separate answer:

=ArrayFormula(IFERROR(TRIM(SPLIT(VLOOKUP(FILTER(A3:A,A3:A<>""),REGEXREPLACE(SPLIT(FLATTEN(QUERY(QUERY(SPLIT(QUERY(FLATTEN({IF(Sheet1!B2:E<>TRUE,,Sheet1!B1:E1&"~|"&Sheet1!A2:A&",");IF(Sheet2!B2:E<>TRUE,,Sheet2!B1:E1&"~|"&Sheet2!A2:A&",")}),"Select * WHERE Col1 Is Not Null"),"|"), "Select MAX(Col2) where Col2 IS NOT NULL GROUP BY Col2 PIVOT Col1"),, 9^9)),"~"),"[,\s] $",""),2,FALSE),","))))

I've added it into your spreadsheet in a new sheet ("Erik Help").

If you have more than two sheets' to include, just find this section of the formula and continue the pattern:

{ IF(Sheet1!B2:E<>TRUE,,Sheet1!B1:E1&"~|"&Sheet1!A2:A&","); IF(Sheet2!B2:E<>TRUE,,Sheet2!B1:E1&"~|"&Sheet2!A2:A&",") }