Lookup from multiple worksheets and return result

[shili12]

No reply ... Is there still an issue here?

No issue, we have left office for home, I check it out tomorrow. Provide solution in worksheet form I don't know how to change to 54050

 

[AliGW]

It's like this:

=LET(reg,VSTACK(start:finish!K2:K54050),
date,VSTACK(start:finish!M2:M54050),
sum_insured,VSTACK(start:finish!U2:U54050),
DROP(REDUCE("",TOCOL(A3:A54050,1),LAMBDA(x,y,
VSTACK(x,TAKE(FILTER(HSTACK(TEXT(date,"Mmmyyyy"),sum_insured),reg=y,"Not Found"),1)))),1))

What is hard about it???

 

[shili12]

Thank you, I can't wait to give to the juniors so hours of work are saved by themselves.
Much obliged for your effort. If every "like" would award you some monetary reward for your effort and time expended.
I meant the 54050 in name manager section, anyway not a problem, I'll just cope with half measures.

 

[AliGW]

The formula doesn't use a named range - the ranges are defined within the formula. It's not a half measure.

Glad to have helped.

 

[p45cal]

If you right-click on the green table in the attached and choose Refresh, it may work out-of-the-box (if you still have this file present on your system:
C:\Users\S.Lakhani\Downloads\askquestion.xlsb )
(Since askquestion.xlsb is a .xlsb file it should not be open whilst refreshing)

Separately, is there any reason both instances of KCP146M don't/shouldn't appear in @AliGW 's formula results?:

 

[AliGW]

Separately, is there any reason both instances of KCP146M don't/shouldn't appear in @AliGW 's formula results?:

They could do, but it would require a rethink of the results layout. I have set the formula to use the first of the two. (TAKE). At no point has the OP made it clear that both results should be shown (they have been very unwilling to properly clarify the expected results throughout, which has made the thread rather protracted).

 

[shili12]

at the risk of being protracted here, just to inform @p45cal , i renamed your worksheet to xlsx, and now its working optimally.
duplicated, triplicate values are ok in results. Am also using the other template too. thanks a lot of time saved for my juniors.
I remembered to change the name in advanced editor to PC's name too.