INDEX MATCH LARGE multiple columns

Hello all,

I'm trying to write a formula that will return the country names of the three highest weight allocations across time in a portfolio.

Column A has the country names, and columns B through BI contain the allocations to various countries over time.

I can get it using additional steps, as I have in K3:K5 with the help of column BJ, but I'd like to learn how to do it without creating extra columns that summarize the row data.

So in essence I want G3 to show "Japan" without the help of column BJ, if that makes sense. It would also be awesome to get the dates of the max values in H3:H5.

I've attached a sample file here to illustrate the issue.

Thanks in advance for any assistance.

-Mike

In G3 copied down

Formula:

Please Login or Register  to view this content.

Thanks so much for your response kv...

Looks like I'm getting Japan for all three...

Do you happen to have a solution that uses Index/Match? I'm trying to better understand the syntax/logic in a way that I can reproduce this later on.

Please try at
F3
=LARGE(MOD(SMALL(SEQUENCE(ROWS(A11:A40))*100+B11:BI40,SEQUENCE(ROWS(A11:A40),,COLUMNS(B11:BI40),COLUMNS(B11:BI40))),100),E3:E5)

G3
=XLOOKUP(F3#,MOD(SMALL(SEQUENCE(ROWS(A11:A40))*100+B11:BI40,SEQUENCE(ROWS(A11:A40),,COLUMNS(B11:BI40),COLUMNS(B11:BI40))),100),A11:A40)

or with LET function
=LET(z,B11:BI40,c,A11:A40,se,SEQUENCE(ROWS(c)),mx,MOD(SMALL(se*100+z,se*COLUMNS(z)),100),FILTER(SORT(CHOOSE({1,2},mx,c),,-1),se<=3))



J3
=LARGE(MMULT(B11:BI40,TRANSPOSE(COLUMN(B11:BI40)^0))/COLUMNS(B11:BI40),I3:I5)

K3
=XLOOKUP(J3#,MMULT(B11:BI40,TRANSPOSE(COLUMN(B11:BI40)^0))/COLUMNS(B11:BI40),A11:A40)

or with LET function
=LET(z,B11:BI40,c,A11:A40,av,MMULT(z,TRANSPOSE(COLUMN(z)^0))/COLUMNS(z),FILTER(SORT(CHOOSE({1,2},av,c),,-1),SEQUENCE(ROWS(c))<=3))


All this is easier with Lambda Byrow function, but the function is only available for insider subscription.

The max values as per your formula in F3:F5 are all belong to Japan.

In F3

=AGGREGATE(14,6,SUBTOTAL(4,OFFSET($B$11:$BI$11,ROW($A$11:$A$40)-ROW($A$11),0)),E3)

In G3

=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$11:$A$50)/(($A$11:$A$50<>"")*($B$11:$BI$50=$F3)),1)),"")

In J3

=AGGREGATE(14,6,SUBTOTAL(5,OFFSET($B$11:$BI$11,ROW($A$11:$A$40)-ROW($A$11),0)),E3)

In K3

=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$11:$A$50)/(($A$11:$A$50<>"")*($B$11:$BI$50=$J3)),1)),"")

copied down

@mflowers You're Welcome. Glad to help . Thank You for the feedback

POST#15 formula ......SUBTOTAL(4 .................... , 4 change to 5 is smallest