Apply complex formula to whole column using MAP or ARRAY FORMULA-CodePudding

This is a followup to my previous question.

@Martin and @rockinfreakshow helped me to convert the following formula using MAP and BYROW respectively.

Formula before conversion in F1 cell:

=if(Iferror(vlookup($D1, INDIRECT("$"&"A"&MATCH(E1,$B:$B,0) 1&":$B"),2,false),"")=E1,"",Iferror(vlookup($D1, INDIRECT("$"&"A"&MATCH(E1,$B:$B,0) 1&":$B"),2,false),""))

Formula solution in F1 cell:

=MAP(D:D,E:E,LAMBDA(d,e,IFERROR(IF(d="","",INDEX(FILTER(B:B,A:A=d,ROW(B:B)>MATCH(e,B:B,0)),1)))))

As I was expecting that the solution would be ARRAYFORMULA wrapping and I would convert the next formula by myself but that was not the case. So I am looking for your help to convert these:

Formula in G1 cell:

=if(or(Iferror(vlookup($D1, INDIRECT("$"&"A"&MATCH(F1,$B:$B,0) 1&":$B"),2,false),"")=E1,Iferror(vlookup($D1, INDIRECT("$"&"A"&MATCH(F1,$B:$B,0) 1&":$B"),2,false),"")=F1)
,"",Iferror(vlookup($D1, INDIRECT("$"&"A"&MATCH(F1,$B:$B,0) 1&":$B"),2,false),""))

and

Formula in H1 cell:

=if(or(Iferror(vlookup($D1, INDIRECT("$"&"A"&MATCH(G1,$B:$B,0) 1&":$B"),2,false),"")=E1,Iferror(vlookup($D1, INDIRECT("$"&"A"&MATCH(G1,$B:$B,0) 1&":$B"),2,false),"")=F1,Iferror(vlookup($D1, INDIRECT("$"&"A"&MATCH(G1,$B:$B,0) 1&":$B"),2,false),"")=G1)
,"",Iferror(vlookup($D1, INDIRECT("$"&"A"&MATCH(G1,$B:$B,0) 1&":$B"),2,false),""))

Now, my expectation is if we are able to find the solution for G1, H1 formulas, I should be able to replicate it for formulas in other cells I1,J1,K1..

I have linked a sheet for your testing: https://docs.google.com/spreadsheets/d/13XLZvvdzK_mqr4Ous50cIEfernw2XrPJWvVgt1hFxtk/edit?usp=sharing

Any help is much appreciated.

CodePudding user response：

That's a very heavy spreadsheet! You may try deleting all your data in E:AU and use this formula:

=ARRAYFORMULA(IFERROR (SPLIT(BYROW(D1:INDEX(D1:D,COUNTA(D1:D)),LAMBDA(v,TEXTJOIN(",",,FILTER(B:B,A:A=v)))),",")))

It joins the filtered values per row, and then splits them in your desired columns

CodePudding user response：

I'm not sure what exactly the formula does but try this:

=ARRAYFORMULA(LAMBDA(res,if(res=E1:E11,,res))(XLOOKUP(D1:D11,A1:A11,B1:B11,,,-1)))