Google sheets match index arrayformula-CodePudding

I have a count using a formula that needs to be stretched (the formula is in cell F9 ) https://docs.google.com/spreadsheets/d/1sZM1aAuqkHNONJWagiu3aTw6_vfw515gbiKwhKuQAD4/edit#gid=1464536028

=IF(ISBLANK(E9);;IF(O9=TRUE;INDEX('Тех Лист'!$F$2:$M$2;MATCH(TRUE;Q9:X9;0));IF(P9=TRUE;INDEX('Тех Лист'!$F$3:$M$3;MATCH(TRUE;Q9:X9;0));"Ошибка!")))

I tried to remake it under Arrayformula, but nothing came of it ... later I got the idea to do it through query, but when I add query to the array, the data I need disappears

=INDEX(split(FLATTEN(TRANSPOSE({'Стадник'!$D9:D&"x"&'Стадник'!$Q8:$X8&"x"&'Стадник'!Q9:X&"x"&'Тех Лист'!$F$2:$M$2&"x"&'Тех Лист'!$F$3:$M$3}));"x"))

Please tell me how I can compare the headers, and if the list is TRUE, take the sum from the table

CodePudding user response：

In G9 I entered

=ArrayFormula(if(len(E9:E); vlookup(trim(transpose(query(transpose(if(Q9:X; Q8:Y8;));;50000)))&""; transpose('Тех Лист'!E1:M3)&""; if(O9:O; 2; if(P9:P; 3;));0) 0;))

Note that this solution requires that only one checkbox can be ticked in the range X9:X.

See if that helps ?