0

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).

enter image description here

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

Please help!

EDIT: Link to sheet

Kevin P.
  • 907
  • 7
  • 18

2 Answers2

2

try:

=INDEX(IFERROR(VLOOKUP(A14:A, SUBSTITUTE(REGEXREPLACE(SPLIT(FLATTEN(
 QUERY(SUBSTITUTE(QUERY(SPLIT(FLATTEN(
 IF({B3:E5; B9:E11}=TRUE, 
 {B2:E2&"♦"&A3:A5&"♣"&ROW(A3:A5); 
  B8:E8&"♦"&A9:A11&"♣"&ROW(A9:A11)}, )), "♦"), 
 "select max(Col2) where Col2 is not null group by Col2 pivot Col1"), 
 " ", "♥"),,9^9)), " "), "(♣\d+)", ), "♥", " "), 
 SEQUENCE(1, COUNTUNIQUE(B2:E2, B8:E8))+1, 0)))

enter image description here

demo sheet

player0
  • 124,011
  • 12
  • 67
  • 124
  • It appears to work in your example but I cannot modify it to fit multiple sheets. As I linked in my original post an example sheet and modified your code to reflect the referenced cells. Also WOW what an insane formula. I wish I could understand half of it... – Kevin P. Oct 15 '21 at 18:21
  • @KevinP. see your sheet – player0 Oct 15 '21 at 18:39
  • Amazing work! I will transpose this into my real world sheet and I have a lot to play with. A couple questions for me to wrap my head around it. Are the key codes there to assign each instance a unique value? If I have 20+ names will I need 20+ identifiers? What's the 9^9 doing? ♣\d+? and the last "♥"? – Kevin P. Oct 15 '21 at 18:43
  • 1
    all you need is to expand the ranges to fit your needs. if you mean by "key codes" the *789 - its just extra stuff to offset ranges. most likely you can drop it. 9^9 is just a ridiculously large number - could be any number you wish if its higher than total rows of all ranges combined. what it does its forces QUERY to compress all rows into one single row. ♥♦♣ are just unique symbols... you can use any symbols you wish for that are not present in your dataset. ♣\d+? is regex expression and it means to remove ♣folowed by any number. – player0 Oct 15 '21 at 19:05
  • 1
    substituting spaces in your dataset for ♥ is due to QUERY(,,9^9) which has a small side effect - by compressing all cells into single one it joins all cells with empty space - which is kinda handy in this case coz we can split it nicely by that empty space (but not the initial spaces ofc coz that would mess up everything so we convert all spaces for hearts do the query smash and split and then convert back hearts into spaces – player0 Oct 15 '21 at 19:09
  • @KevinP. more info on query smash & 9^9 can be seen here: https://stackoverflow.com/a/65435321/5632629 – player0 Oct 15 '21 at 19:11
1

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&",") }

Erik Tyler
  • 9,079
  • 2
  • 7
  • 11
  • Thank you so much! I like not having to rely on emoticons or char() strings. It helps dissect this formula for use on SEVERAL sheets. I was way off on my example formula... At least the remaining 75% lol. – Kevin P. Oct 15 '21 at 18:53
  • 1
    You're welcome. It really doesn't matter what the temporary join/split character is, as long as it is certain never to turn up in actual sheet data (in your case, names or check labels). That is why you'll often see characters like player0's club symbol: because we know that will never turn up. I tend to use pipe symbols (unless that character will be used somewhere in the formula within REGEX-type expressions) or a tilde. But it's just a matter of personal style. – Erik Tyler Oct 15 '21 at 18:59
  • I noticed that I can't adjust the range past A:Z in the first two parameters. Is there something I am missing or is that just the limitations of Sheets? For example I have: `{ IF(Sheet1!B2:AZ<>TRUE,,Sheet1!B1:AZ1&"~|"&Sheet1!A2:A&","); IF(Sheet2!B2:AZ<>TRUE,,Sheet2!B1:AZ1&"~|"&Sheet2!A2:A&",") }` And it breaks. – Kevin P. Oct 16 '21 at 00:08
  • Keep in mind that we can't see what you are seeing. You're only showing part of the formula here. And we don't know what the error is (only "it breaks"). So there's not much more I can tell you without either seeing the full formula as you've adapted it or the spreadsheet itself (preferably both). – Erik Tyler Oct 16 '21 at 00:36
  • Completely understand, when I made this post I had no idea I would be outside the range of A:Z until I started making the sheet. I had not yet created it. The code you provided works perfect until I expand the range outside A:Z (all other ranges are fine when changing). Would you like a link to a copy of my sheet? – Kevin P. Oct 16 '21 at 00:43
  • That would be the fastest way to assess and help solve the issue. There should be no problem with the expanded range, so you're probably just mistyping something simple in the formula. – Erik Tyler Oct 16 '21 at 00:46
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/238193/discussion-between-erik-tyler-and-kevin-p). – Erik Tyler Oct 16 '21 at 01:40
  • Apologies! Link should work now. https://docs.google.com/spreadsheets/d/1EcNIZ2Xvhh1ZLHb__zJ0hFJJl2QAHUcapmQj3Ug4teI/edit?usp=sharing – Kevin P. Oct 16 '21 at 01:43