Excel Forum

Hi there
I need your input how to get year match the winner, the latest year .

I attach a template with expected results

Thx

The example formulas are below:

1. G3 Type formula for generating all of values that meet condition from column A.
   =IFERROR(INDEX($D:$D,AGGREGATE(15,6,ROW($D$3:$D$24)/($E$3:$E$24=$A3),COLUMNS($G3:G3))),"")
   Enter > Copy to the right-hand side until K3 and Copy down
2. B3 Type formula for the result.
   =SUBSTITUTE(TRIM(SUBSTITUTE(G3&", "&H3&", "&I3&", "&J3&", "&K3,", "," "))," ",", ")
   Enter > Copy down

Note: Formulas above suitable for your Excel version in your personal information in this time.

Thanks.how about excel 365?

B3 type this formula

=MAP(A3:A10,LAMBDA(a,TEXTJOIN(", ",TRUE,FILTER(D3:D24,E3:E24=a))))

Please ensure your personal information includes the updated Excel version to receive the appropriate formulas.

No ,I have data like this
Winners Years
Argentina 2022, 1986, 1978
France 2018, 1998
Germany 2014, 1990, 1974, 1954
Spain 2010
Italy 2006, 1982, 1938, 1934
Brazil 2002, 1994, 1970, 1962, 1958
England 1966
Uruguay 1950, 1930

I need answer like this
Answer Expected
Year Winner
2022 Argentina
2018 France
2014 Germany
2010 Spain
2006 Italy
2002 Brazil
1998 France
1994 Brazil
1990 Germany
1986 Argentina
1982 Italy
1978 Argentina
1974 Germany
1970 Brazil
1966 England
1962 Brazil
1958 Brazil
1954 Germany
1950 Uruguay
1938 Italy
1934 Italy
1930 Uruguay

Please update your profile with the latest Excel version information.

I update already

The expected answers in column D& E

The example formula in D3 is.

=SORT(LET(a,TRIM(TOCOL(TEXTSPLIT(CONCAT(B3:B10&",",","),","))),DROP(HSTACK(a,INDEX(A3:A10,MATCH("*"&a&"*",B3:B10,0))),-2)),,-1)

Note: If you are using multiple versions of Excel, you can specify each version accordingly.

Thanks so much