ArrayFormula to find the last matching value-CodePudding

I am using below formula to find the last matching value
but how can i expand it with ArrayFormula
=index(filter(A:A,B:B=F3),SUMPRODUCT(B:B=F3))

I expect to have a arrayformula to work with index and filter but couldnt

CodePudding user response：

Try with BYROW:

=BYROW(F3:F,LAMBDA(each(if(each="","",index(filter(A:A,B:B=each),SUMPRODUCT(B:B=eacg)))))

You could also use XLOOKUP (-1 in search mode) with ARRAYFORMULA:

=ARRAYFORMULA (XLOOKUP(F3:F,B:B,A:A,"",0,-1))

CodePudding user response：

try:

=INDEX(XLOOKUP(F3:F, B:B, A:A,,,-1))

or:

=BYROW(F3:F, LAMBDA(f, INDEX(FILTER(A:A, B:B=f), SUMPRODUCT(B:B=f))))