Create a Dynamic Array formula (Excel) to combine multiple results columns into one column that is filtered & sorted using multiple criteria?

Question

The sample data in the image below is collected from a round robin tournament.

There is a Round column,Home team & Away team columns listing who is playing who. A team could be either Home or Away.

For each match in a round (including any "Bye" match) the number of games won for the Home and Away team are recorded in separate columns respectively. "Ff" = forfeit and has a value of 0. "Bye" result is left blank (at this stage).

Output columns are "Won, Lost, Round".

Required output (shown in the image) is, for any selected team, the top n most-games-won matches (from both Home & Away) sorted in descending order and then the corresponding games lost but sorted in ascending order where the games won are equal. Finally show the rounds where those scores occurred.

These are the challenges I've faced in going from data to output in one step using dynamic array formula:

1. Collating/Combining the the Win results into 1 column. Likewise the Losses.

2. Getting the array to ignore blanks or convert "Ff" to 0 without getting #NUM or #VALUE errors.

3. Ensuring that if I used separate single column arrays the corresponding Loss and Round matched the Win result

4. Although "Round, Won, Lost" would be acceptable. But I wasn't able to get the Dynamic Array capability to give the required output with this order.

SUMPRODUCT, INDEX(MATCH), SORT(FILTER) functions all hint at a possible one step formula solution.

The solutions are numerous for sorting & filtering where the existing values are already in one column. There was one solution that dealt with 2 columns of values which was somewhat useful How to get the highest values from 2 columns in excel - Stackoverflow 2013 Many other responses are around the use of concatenation, combining/merging array sets, aggregation etc.

My work around solution is to use a Helper Sheet to combine the Wins from the separate results columns and convert blanks & "Ff" to -1. Likewise for Losses. Using the formula for each line =IF($C5=L$2,IF($F5="",-1,IF($F5="Ff",0,$F5)),IF($D5=L$2,IF($G5="",-1,IF($G5="Ff",0,$G5)),-1))

Example Helper Sheet

To get the final output the Dynamic Array formula was used on the Helper Sheet data =SORT(FILTER(L$26:N$40,L$26:L$40>=LARGE(L$26:L$40,$J$3),""),{1,2},{-1,1},FALSE)

I'm trying to avoid using pivottable, VBA solutions. Powerquery possible but not preferred.

Apologies for the screenshots but I couldn't work out how to attach the sample spreadsheet file. (Unfortunately Stackoverflow Help didn't help me to/not to do this.)

Answer 1

Based on the comments I changed my answer with a different approach:

=LET(data,A5:F19,
     round,INDEX(data,,1),
     ha,CHOOSECOLS(data,3,4),
     HAwonR,CHOOSECOLS(data,5,6,1),
     w,BYROW(ha,LAMBDA(h,IFERROR(XMATCH(L2,h),0))),
     clm,CHOOSE(w,{1,2},{2,1}),
     srtwon,DROP(REDUCE(0,SEQUENCE(ROWS(data)),LAMBDA(y,z,VSTACK(y,INDEX(HAwonR,z,HSTACK(INDEX(clm,z,),3))))),1),
     res,FILTER(srtwon,w),
TAKE(SORT(res,{1,2},{-1,1}),J3))

Old answer:

=LET(data,A5:F19,
     round,INDEX(data,,1),
     home,INDEX(data,,3),
     away,INDEX(data,,4),
     HAwonR,CHOOSECOLS(data,5,6,1),
     w,MAP(home,away,LAMBDA(h,a,OR(h=L2,a=L2))),
     won,FILTER(HAwonR,w),
TAKE(SORT(won,{1,2},{-1,1}),J3))

In your example you selected round 3 for the third result, but that wasn't won, so I guess that was by mistake.

As you can see making use of LET avoids helpers. Let allows you to create names (helpers) that are stored and because you can name them, you can make complex formulas be more readable. Basically what it does is filter the columns Home, Away and Round (in that order) for either Home or Away equal the team in cell L2. That's sorted column 1 descending and column 2 ascending. Than the number of rows mentioned in cell J3 are displayed from that sorted array.