Formula Aggregate or index and compare

[Berna11]

Good evening, I have a problem carrying data from one sheet to another with the Aggregate formula
in the file attached in sheet 2 I should report the names found in the Teams sheet in row 1 when there is a correspondence with the name in column C.
But, in cell M15 it doesn't give me the name in AC1 team sheet.
Furthermore, I can't find which formula to use to get the corresponding data that I put by hand in column N.
Thanks

[XLent]

so, a couple of issues

your setup for SQUADRA10 on Teams sheet is inconsistent with 1-9, so:

first - SQUADRA10 header should be in AB1 rather than AC1 (AC1 should be empty)
second - BARAK in AB2 should be "P" (currently listed in both AB2 & AC2, only latter is valid)

once you have fixed the above... regards Sheet2 calcs:

M2: =IF(C2="";"";IFERROR(INDEX(Teams!$A$1:$AB$1;AGGREGATE(15;6;(COLUMN($B$1:$AC$1)-1)/((Teams!$B$2:$AC$29=C2));COLUMN(A1)));""))

N2: =IF($M2="";"";SUMPRODUCT((Teams!$B$2:$AC$29=$C2)*(Teams!$A$1:$AB$1=$M2);Teams!$C$2:$AD$29))

copied down

the SUMPRODUCT could be replaced with a SUMIF if you're only ever going to find a "player" in one "squad" -- the SUMPRODUCT assumes this may not be the case (could be in multiple squads whereas you only want value of first found)

[Berna11]

Thank you thank you ...
now it works.

[Berna11]

Hi,
I apologize if I continue here
If possible, help to do the reverse of what was done before.

That is, having the data entered by hand in the sheet 2 columns M-N
bring them into the Teams sheet
Thanks

[JohnTopley]

What determines the ROW in each sub-group (D,P,A,C)the team sheet?

I would re-arrange your table so you only have the codes (P,C.D A) in column A: they are redundant in the other columns AND remove merged cells

And you really should start a new thread as this is a different problem.

[Berna11]

OK thanks,
I open new thread.