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

Improve this question
2
  • can names repeat in your both tables? Commented Oct 15, 2021 at 17:20
  • I added a link to a sheet. Names can repeat yes but not in the same sheet. Just across sheets, and check1, check2, etc will always be unique Commented Oct 15, 2021 at 17:22

2 Answers 2

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

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...
@KevinP. see your sheet
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 "♥"?
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.
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
|
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&",") }

Improve this answer

8 Comments

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.