+ Reply to Thread
Results 1 to 19 of 19

INDEX MATCH LARGE multiple columns

  1. #1
    Registered User
    Join Date
    12-07-2012
    Location
    California
    MS-Off Ver
    Excel 365
    Posts
    24

    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
    Attached Files Attached Files
    Last edited by flizzo; 09-22-2021 at 01:02 PM. Reason: solved

  2. #2
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,295

    Re: INDEX MATCH LARGE multiple columns

    In G3 copied down

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 09-14-2021 at 11:15 AM.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  3. #3
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Malaysia
    MS-Off Ver
    365 & WPS(2022)
    Posts
    2,250

    Re: INDEX MATCH LARGE multiple columns

    Cell G3 array formula , Drag down
    HTML Code: 

  4. #4
    Registered User
    Join Date
    12-07-2012
    Location
    California
    MS-Off Ver
    Excel 365
    Posts
    24

    Re: INDEX MATCH LARGE multiple columns

    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.
    Last edited by flizzo; 09-14-2021 at 11:51 AM.

  5. #5
    Registered User
    Join Date
    12-07-2012
    Location
    California
    MS-Off Ver
    Excel 365
    Posts
    24

    Re: INDEX MATCH LARGE multiple columns

    Thanks wk9128 for your assistance here. I'm getting Japan for all three here as well (the second and third should be the UK and Switzerland).

    Is there a way to use Index / Match? Hoping for a solution that I understand and can use again. Much appreciated.

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    12,452

    Re: INDEX MATCH LARGE multiple columns

    mflowers are you still using xl2007? as it doesn't have the aggregate function?
    If not what version of Excel are you using?

  7. #7
    Registered User
    Join Date
    12-07-2012
    Location
    California
    MS-Off Ver
    Excel 365
    Posts
    24

    Re: INDEX MATCH LARGE multiple columns

    Thanks Fluff13. Looks like I have Excel for Microsoft 365 MSO. Under About Excel it also says Version 2008.

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    12,452

    Re: INDEX MATCH LARGE multiple columns

    Yup that will be 365, please update your profile to let members know. Thanks

  9. #9
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    12,452

    Re: INDEX MATCH LARGE multiple columns

    I don't think you will be able to get the results using index/match without the helper columns.

  10. #10
    Registered User
    Join Date
    12-07-2012
    Location
    California
    MS-Off Ver
    Excel 365
    Posts
    24

    Re: INDEX MATCH LARGE multiple columns

    Thanks for the feedback, and will update my profile, thank you.

  11. #11
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    12,452

    Re: INDEX MATCH LARGE multiple columns

    Is there a possibility that you could get two max values which are the same?

  12. #12
    Registered User
    Join Date
    12-07-2012
    Location
    California
    MS-Off Ver
    Excel 365
    Posts
    24

    Re: INDEX MATCH LARGE multiple columns

    Thx Fluff13, it could happen but seems unlikely, as the values go out many decimal places.

  13. #13
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    12,452

    Re: INDEX MATCH LARGE multiple columns

    OK, if two numbers are the same then index/match won't work.
    2 options
    1) in F3 down use
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and in G3 use
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    2) Using dynamic functions
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I've put it in H3
    Attached Files Attached Files

  14. #14
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,060

    Re: INDEX MATCH LARGE multiple columns

    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.
    Attached Files Attached Files

  15. #15
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Malaysia
    MS-Off Ver
    365 & WPS(2022)
    Posts
    2,250

    Re: INDEX MATCH LARGE multiple columns

    Cell F3 formula
    HTML Code: 
    Cell G3 formula
    HTML Code: 

  16. #16
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,295

    Re: INDEX MATCH LARGE multiple columns

    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
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    12-07-2012
    Location
    California
    MS-Off Ver
    Excel 365
    Posts
    24

    Re: INDEX MATCH LARGE multiple columns

    Thanks to everyone who helped here, y'all are awesome.

  18. #18
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    12,452

    Re: INDEX MATCH LARGE multiple columns

    Glad to help & thanks for the feedback.

  19. #19
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Malaysia
    MS-Off Ver
    365 & WPS(2022)
    Posts
    2,250

    Re: INDEX MATCH LARGE multiple columns

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

    POST#15 formula ......SUBTOTAL(4 .................... , 4 change to 5 is smallest
    Last edited by wk9128; 09-16-2021 at 10:46 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] INDEX MATCH? Multiple results from large data
    By joppert87 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-30-2020, 06:43 AM
  2. INDEX MATCH & LARGE w/ multiple criteria
    By barrenaj in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-15-2019, 05:32 PM
  3. [SOLVED] INDEX MATCH & LARGE Functions, Multiple Criteria
    By skyhawk3485 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 12-19-2018, 09:55 AM
  4. [SOLVED] INDEX MATCH & LARGE functions, multiple criteria
    By abulkhairi in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-11-2017, 01:20 AM
  5. [SOLVED] INDEX MATCH LARGE with Multiple criteria
    By clachi80 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-14-2017, 03:50 AM
  6. Index Match Large formula with multiple criteria
    By Dylan Cooper in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-21-2016, 06:00 PM
  7. Excel 2007 : Index, Match, Large Formula: Multiple Criteria, Multiple Ranges
    By SimpleJack in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-19-2013, 08:54 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1