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?
CodePudding user response：
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".