Large, Index, Match with no helper column. Is it possible?

[slilesy]

Hi,
New to this forum so hope this makes sense.

Is it possible to use LARGE, INDEX and MATCH together in a single formula with no helper column?

For the above:

I need to be able to have two groups of 4 names from any of the 8 names.
Group 1 and group 2 are just examples I have chosen but it could be any combination of names in the groups.
For each group of four names I want to be able to look up each name and their score and then sum the largest two scores for the group.
I am trying to find a formula that does not use a helper column.

Thanks for any help

Steve

 

[Fluff]

How about

=SUM(LARGE(SUMIFS(C84:J84,C83:J83,L84:L87),{1,2}))

 

[Kaper]

This is how I understood the problem:

The formula in L92 (or any empty cell ) and copied right:

=LET(mycolumns,IF(COUNTIF(L$84:L$87,$C$83:$J$83)=1,$C$84:$J$92,""),LARGE(mycolumns,1)+LARGE(mycolumns,2))

or modified using an internal array like in Fluff's answer:

=LET(mycolumns,IF(COUNTIF(L$84:L$87,$C$83:$J$83)=1,$C$84:$J$92,""),SUM(LARGE(mycolumns,{1;2})))

or even further:

=SUM(LARGE(IF(COUNTIF(L$84:L$87,$C$83:$J$83)=1,$C$84:$J$92,""),{1;2}))

(the xl2bb snippet below is with the first one, but try also the above modifications/shortening)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
82
83			Name1	Name2	Name3	Name4	Name5	Name6	Name7	Name8		group1	group2
84		Score1	646	18	1007	252	161	164	45	494		Name1	Name2
85		Score2	168	100	98	364	370	47	493	496		Name5	Name8
86		Score3	364	91	1004	169	641	14	368	91		Name3	Name4
87		Score4	812	491	42	494	168	492	260	256		Name6	Name7
88		Score5	166	815	642	16	47	816	260	15
89		Score6	643	644	255	20	254	361	93	370
90		Score7	819	259	819	1002	1008	167	165	100
91		Score8	92	817	650	258	162	497	94	162
92		Score9	168	495	365	819	165	649	1007	365		2015	2009
93
Sheet1

Cell Formulas
Range	Formula
L92:M92	L92	=LET(mycolumns,IF(COUNTIF(L$84:L$87,$C$83:$J$83)=1,$C$84:$J$92,""),LARGE(mycolumns,1)+LARGE(mycolumns,2))

 

[slilesy]

How about

=SUM(LARGE(SUMIFS(C84:J84,C83:J83,L84:L87),{1,2}))

Thanks Fluff, that's solved my problem.

 

[slilesy]

This is how I understood the problem:

The formula in L92 (or any empty cell ) and copied right:

=LET(mycolumns,IF(COUNTIF(L$84:L$87,$C$83:$J$83)=1,$C$84:$J$92,""),LARGE(mycolumns,1)+LARGE(mycolumns,2))

or modified using an internal array like in Fluff's answer:

=LET(mycolumns,IF(COUNTIF(L$84:L$87,$C$83:$J$83)=1,$C$84:$J$92,""),SUM(LARGE(mycolumns,{1;2})))

or even further:

=SUM(LARGE(IF(COUNTIF(L$84:L$87,$C$83:$J$83)=1,$C$84:$J$92,""),{1;2}))

(the xl2bb snippet below is with the first one, but try also the above modifications/shortening)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
82
83			Name1	Name2	Name3	Name4	Name5	Name6	Name7	Name8		group1	group2
84		Score1	646	18	1007	252	161	164	45	494		Name1	Name2
85		Score2	168	100	98	364	370	47	493	496		Name5	Name8
86		Score3	364	91	1004	169	641	14	368	91		Name3	Name4
87		Score4	812	491	42	494	168	492	260	256		Name6	Name7
88		Score5	166	815	642	16	47	816	260	15
89		Score6	643	644	255	20	254	361	93	370
90		Score7	819	259	819	1002	1008	167	165	100
91		Score8	92	817	650	258	162	497	94	162
92		Score9	168	495	365	819	165	649	1007	365		2015	2009
93
Sheet1

Cell Formulas
Range	Formula
L92:M92	L92	=LET(mycolumns,IF(COUNTIF(L$84:L$87,$C$83:$J$83)=1,$C$84:$J$92,""),LARGE(mycolumns,1)+LARGE(mycolumns,2))

Thanks for the reply Kaper,
I just need the largest two for the group from each row so modifying your formula to the following for the array from $C$84:$J$92 to $C$84:$J$84 also does the job for me:

=LET(mycolumns,IF(COUNTIF(L$84:L$87,$C$83:$J$83)=1,$C$84:$J$84,""),LARGE(mycolumns,1)+LARGE(mycolumns,2))

 

[Fluff]

Glad we could help & thanks for the feedback.