Array formula is not working with the combination of Vlookup and Indirect-Match?-CodePudding

I am trying to generate an arrayformula for the whole column but it does not work, when I use the same formula cell by cell, it generates correct value. Here is the formula for 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),""))

I am trying to convert it into ArrayFormula like this and put it in F1:

=ARRAYFORMULA(if(Iferror(vlookup($D:$D, INDIRECT("$"&"A"&MATCH($E:$E,$B:$B,0) 1&":$B"),2,false),"")=$E:$E,"",Iferror(vlookup($D:$D, INDIRECT("$"&"A"&MATCH($E:$E,$B:$B,0) 1&":$B"),2,false),"")))

But this does not work and returns empty column, here is the sheet if you want to test it, you can find formula in column F:

CodePudding user response：

Unclear of the full scope but from your expected result scenario(in Column F) you seem to be pulling off second match(if any). well in that case try:

=BYROW(D:D,LAMBDA(dx,IF(dx="",,IFNA(FILTER(ARRAY_CONSTRAIN(FILTER(B:B,A:A=dx),2,1),{0;1})))))