Referencing Spilled Array to Create Spilled Array

I have a dataset that is a list of Sellers, years and values in a table called ?tData? [A2:C13]. Example data has only 2 names and 3 years, but actual data could have more.
I also have a named range that produces scores for Sellers in different years, which is a spilled array from the named range ?scoreArray?[U3#]. (arbitrary score values are used for the example)

Given a Seller name in [E2], I need to produce a list of years that are in the data for that name, stored in named range ?YearList? [E5#]
For each year, I need to sum all the value of their sales, and compare to the sales of the Sellers that sold the minimum amount that year.
Obviously, the number of years to be displayed and the number of Sellers involved changes depending on the dataset. My solution was to use dynamically sized spilled arrays that resize to match the unique entries in the dataset.
Now I can get formulas to work if I set manually fill down formulas to the correct number of rows. I can reference the scoreArray# and get a single result.

But I can't work out to get a spilled array that will get the correct number of rows dynamically by referencing YearList#. I would appreciate advice on fixing the formulas so that I can get spilled range outputs to match the expected output.

I have attached my example workbook. Range to be corrected in is Yellow (G5:K8)
Expected output is shown in Green (M5:Q8)

Formulas:
G5

Formula:

Please Login or Register  to view this content.

I5

Formula:

Please Login or Register  to view this content.

J5

Formula:

Please Login or Register  to view this content.

K5

Formula:

Please Login or Register  to view this content.

How have you derived these values?

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	M
5	1
6	0.5
7	0.3

Sheet: Sheet1

Whoop.
I altered the value set, and I must have had a couple that I manually overwrote the formula. I'll fix it

You'll what?

Sorry I mistyped and fumbled the "post repy" button.
I have reuploaded the file with the formulas corrected.

Does this adequately show your needs?

What is the purpose of identical columns 1 and 3, and 2 and 5?

This produces the results you want, but probably not for the reason you intend:

=LET(y,YearList#,a,tData[Seller],b,tData[Report Year],c,tData[Sales],d,SUMIFS(c,a,a,b,b),u,UNIQUE(HSTACK(a,b,d)),f,FILTER(u,INDEX(u,,1)=targetName),HSTACK(TRANSPOSE(FILTER(scoreArray#,nameList#=targetName)),INDEX(f,,3),TRANSPOSE(FILTER(scoreArray#,nameList#=targetName)),INDEX(f,,1),INDEX(f,,3)))

PLease try

PHP Code: 

=LET(t,targetName,a,tData[Seller],b,tData[Report Year],c,tData[Sales],y,YearList#,
Fy,LAMBDA(y,XLOOKUP(y,yrs_trans#,scoreArray#)),
Ft,LAMBDA(a,b,MAP(a,b,LAMBDA(y,t,XLOOKUP(t,nameList#,Fy(y))))),
n,MAP(y,LAMBDA(x,XLOOKUP(0,Fy(x),nameList#,,1))),
HSTACK(Ft(y,IF(y,t)),SUMIFS(c,a,t,b,y),Ft(y,n),n,SUMIFS(c,a,n,b,y))) 


Sorry I was in a meeting.
Also sorry, I thought I had checked that example file well, but it was still full of hard coded values that were messing up the example.
I uploaded a corrected copy (again )
I;ll look at your solutions and see how I go.
Thanks.

Explain this:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	Q
4	Min Name Value
5	0
6	0.819124
7	0.249082

Sheet: Sheet1

I'll leave you in Bo_Ry's hands - he clearly gets what you are trying to do, and I just don't - sorry.

@AliGW and @Bo_Ry
Sorry for delays in my response. I had some stuff going on.
I notice that both of you used HSTACK() to stick together multiple arrays and create a single, weird 2D spilled array from a single formula.

This is not necessary.
I just single formulas for each column.

Thus for I5 I want a formula that spills I5:I7, or any many years as there are, listing the minimum value found in the scoring table for that year.

AliGW solution doesn’t work, as it merely looks up the targetName.
(Please not that I edited AliGW's formula to make it more readable for myself)

Formula:

Please Login or Register  to view this content.

For J5, I want the name that matching the minimum score found in column I, spilled J5:J7.
AliGW’s solution again filtered for the targetName, not the minimum score.

Formula:

Please Login or Register  to view this content.

I also note that your SUMIFS() used the exact same variables both for the Criteria and the comparison values. Could you explain how this works?

Finally for K5, I want the SUMIFS value for the sales of the name that the minimum score, spilling to K5:K7


Bo_Ry is producing entirely correct results, but I am afraid that I really can’t follow his logic on the use of the ILAMBDA().
Bo_Ry, would you be able to explain how your formulas work, as I am lost. I noticed that you used the same letters for parameter in both the LET() and ILAMDA() functions in the same formula. Is this normal?

This is not necessary.
I just single formulas for each column.

You can do what you like to 'improve' any solutions offered.

I also note that your SUMIFS() used the exact same variables both for the Criteria and the comparison values. Could you explain how this works?

I said this:

I'll leave you in Bo_Ry's hands - he clearly gets what you are trying to do, and I just don't - sorry.

There's no point in explaining something that doesn't do what you want. I did also say this when I tentatively offered the proposal:

This produces the results you want, but probably not for the reason you intend:

I am sure that Bo_Ry will explain his formula to you when he sees this.

Simple formula

G
=XLOOKUP(YearList#,yrs_trans#,XLOOKUP(targetName,nameList#,scoreArray#))

I
=MAP(YearList#,LAMBDA(y,MIN(XLOOKUP(y,yrs_trans#,scoreArray#))))

J
=MAP(YearList#,LAMBDA(y,XLOOKUP(0,XLOOKUP(y,yrs_trans#,scoreArray#),nameList#,,1)))

K
=SUMIFS(tData[Sales],tData[Seller],J8#,tData[Report Year],YearList#)

Originally Posted by AliGW

You can do what you like to 'improve' any solutions offered.

Yes. I was trying to clarify. In the new example file I included, I included your solution which helped me with a couple of the columns.
I renamed or removed some of the LET variable to make it easier for my mind to follow.
I also want to be able to apply the the columns formulas in similar places, so I removed the top level HSTACK, and I thought I'd add a comment explaining why I did that.


I feel like your ability to answer my question well was kind of hamstrung by my initial mistakes in communication and mistakes in my example file.
To be honest, even though your answer gave me some stuff I wasn't looking for, it also gave me several lessons in "Huh, I didn't know I could do that and I just learned something."
I would really like to thank you for the effort you put in.

There's no point in explaining something that doesn't do what you want. I did also say this when I tentatively offered the proposal:

Yes. But at the same time you were so close, just clear aiming at the wrong target thanks to my miscommunication. And yours were so easy to understand, so I thought it was worth clarifying. I have just seen Bo_Ry comment, so I will go see what I can learn from them. Thanks again

THANK YOU!

I had never used MAP, and barely touched LAMBDA. Clearly I need to expand my knowledge on these.
Thank you so much for making theme simple enough that I am able to follow and catch up a bit!