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

Question

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:

https://docs.google.com/spreadsheets/d/13XLZvvdzK_mqr4Ous50cIEfernw2XrPJWvVgt1hFxtk/edit?usp=sharing

Please share your knowledge how to go about it as I am trying to learn formulas starting from basic level? Thank you.

Answer 1

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})))))

Answer 2

when working with more than one condition, you can usually use FILTER for it. In this case, I used MAP to refer to both columns D and E, and used FILTER to narrow to the matches in A column, and then to those values that were further than the row of the MATCH of E value. Then, with INDEX I chose the first value of that resulting range.

=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)))))