Arrayformula from Arrayformula

Question

I'm stuck with a formula. I have a sheet with tick boxes to see if a product (unique via manufacture code) is compatibel with a machine.

These product codes are transposed from a different tab in the first row (C1:BD1) in the column B2:B is a Importrange from a different sheet with the machine names.

So I used data validation to generate tick boxes from C2:BD. So far so good. This works all great.

Next if a value is true I need the machine name or names from that product.

=ARRAYFORMULA(textjoin(",",true,((IF(((COUNTIF(True, INDIRECT(CONCATENATE("'Toestellen database'!",(substitute(address(1,(MATCH((HLOOKUP(A5,'Toestellen database'!$C$1:$BD$1,1,false)),'Toestellen database'!$1:$1,0)),4),"1","")),"$2:",(substitute(address(1,(MATCH((HLOOKUP(A5,'Toestellen database'!$C$1:$BD$1,1,false)),'Toestellen database'!$1:$1,0)),4),"1","")))))))=1,'Toestellen database'!$B$2:B,"")))))

So I used this formule (B colum) in tab compatibel met where I have al the product codes in column A to see if a tick box is true or false in the Toestellen database tab.

Works like I intended. If you tick something on or off it appears in the compatibel met tab.

If I copy this code down in the B column A5 will change so that it will look at the next product code and see if the tick boxes are true or false. This is the part that I'm stuck with. I want tot create a ARRAYFORMULA in column B so that the code above will work for al the product codes from A5:A.

Can't get it to work. I'm missing something and I don't know what. Can someone point me in the right direction?

example

textjoin does not work with arrayformula. share a copy/sample of your sheet — player0
– player0, Commented Mar 10, 2022 at 12:07
Can you share a Minimal, Reproducible Example of your sheet? — Kessy
– Kessy, Commented Mar 10, 2022 at 12:21
Keep in mind that while you are explaining things while viewing a spreadsheet and knowing your own end goal, we can't see that same spreadsheet nor do we know your end goal. Please share a copy of your spreadsheet (or a copy of it). — Erik Tyler
– Erik Tyler, Commented Mar 10, 2022 at 12:22

MattKing · Accepted Answer · 2022-03-10 15:33:20Z

2

New tab on your shared sheet called MK.Help has this formula in cell B5. I don't think your old formula was very useful for trying to make into an arrayformula.

=ARRAYFORMULA(IFERROR(VLOOKUP(A5:A,SPLIT(TRANSPOSE(SUBSTITUTE(TRIM(QUERY({'Toestellen database'!C1:1&"|";IF('Toestellen database'!C2:10000,'Toestellen database'!B2:B&CHAR(10),)},,9^9)),CHAR(10),",")),"| ",0),2,0)))

This formula uses what I (and many others) call the QUERY() "smush". You are taking advantage of the 3rd, often unused parameter in the query funciton to concatenate all the "header" rows of a range, but you're instructing it that you have 9 to the 9th power (9^9) header rows. Which is just an easy way of writing "infinite".

answered Mar 10, 2022 at 15:33

MattKing

7,80310 silver badges13 bronze badges

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

1 Comment

cd_BE Over a year ago

Thank you. I was thinking of using the QUERY but couldn't figure it out. Hence my formule. Thought I was on the right track but your formula looks more elegant it wat I was trying to do and it works as a arrayformula.

Collectives™ on Stack Overflow

Arrayformula from Arrayformula

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related