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

slilesy

New Member
Joined
Feb 23, 2025
Messages
1
Office Version
  1. 365
Platform
  1. MacOS
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?

1740341486649.png




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
 
How about
Excel Formula:
=SUM(LARGE(SUMIFS(C84:J84,C83:J83,L84:L87),{1,2}))
 
Upvote 0
This is how I understood the problem:

The formula in L92 (or any empty cell :-)) and copied right:
Excel Formula:
=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:
Excel Formula:
=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:
Excel Formula:
=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
ABCDEFGHIJKLMN
82
83Name1Name2Name3Name4Name5Name6Name7Name8group1group2
84Score164618100725216116445494Name1Name2
85Score21681009836437047493496Name5Name8
86Score33649110041696411436891Name3Name4
87Score481249142494168492260256Name6Name7
88Score5166815642164781626015
89Score66436442552025436193370
90Score781925981910021008167165100
91Score89281765025816249794162
92Score9168495365819165649100736520152009
93
Sheet1
Cell Formulas
RangeFormula
L92:M92L92=LET(mycolumns,IF(COUNTIF(L$84:L$87,$C$83:$J$83)=1,$C$84:$J$92,""),LARGE(mycolumns,1)+LARGE(mycolumns,2))
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top