Page 1 of 2 1 2 LastLast
Results 1 to 10 of 19

Thread: Please help

  1. #1
    Seeker Catherine Coleltt's Avatar
    Join Date
    Sep 2020
    Location
    United Kingdom
    Posts
    9
    Articles
    0
    Excel Version
    2019 & Google Sheets

    Please help



    Register for a FREE account, and/
    or Log in to avoid these ads!

    i'VE CREATED A SPREADSHEET FOR TEAM POINTS IN SCHOOL

    i'VE PUT IN THE FOLLOWING FORMULA TO LOOK AT THE TEAM POINTS AND DELIVER ME THE WINNING TEAM IN A SPECIFIC CELL.

    =INDEX(B$2:E$2,1,MATCH(MAX(B3:E3),B33,0))


    BUT IT ONLY DELIVERS ME ONE TEAM. TWO OF THE TEAMS ARE TIED SO HOW DO I GET IT TO DELIVER 2 NAMES??

    THANKYOU

  2. #2
    Acolyte Bernie's Avatar
    Join Date
    Feb 2018
    Location
    Suburban NY
    Posts
    33
    Articles
    0
    Excel Version
    Excel 2016 (Win)
    Use this array-entered formula, entered using Ctrl-Shift-Enter.

    I'm not sure which range addresses require absolutes since you did not show example data.

    Copy to the right for one or more cells, depending on how many ties you can expect, and then copy down(?)

    =IFERROR(INDEX($2:$2,SMALL(IF($B3:$E3=MAX($B3:$E3),COLUMN($B3:$E3)),COLUMN(A1))),"")
    Bernie Deitrick
    MS Excel MVP 2001-2010

  3. #3
    Seeker Catherine Coleltt's Avatar
    Join Date
    Sep 2020
    Location
    United Kingdom
    Posts
    9
    Articles
    0
    Excel Version
    2019 & Google Sheets
    Bernie
    I did as you said but i got the same answer

    I keep trying to show you the spreadsheet but it says it's too many characters.

    (I' new to this forum stuff can you tell!)

  4. #4
    Acolyte Bernie's Avatar
    Join Date
    Feb 2018
    Location
    Suburban NY
    Posts
    33
    Articles
    0
    Excel Version
    Excel 2016 (Win)
    Catherine,

    The attached file will return the names from row 2 that match the max in row 3 - every time the workbook calculates (use F9 to re-calc) you should get different results in all the cells.

    Give it a try - if that is not the functionality that you were looking for, reply and use the "Go advanced" button and use the "Manage attachments" section to upload an example file with the results that you want based on example data.
    Attached Files Attached Files
    Bernie Deitrick
    MS Excel MVP 2001-2010

  5. #5
    Seeker Catherine Coleltt's Avatar
    Join Date
    Sep 2020
    Location
    United Kingdom
    Posts
    9
    Articles
    0
    Excel Version
    2019 & Google Sheets
    Quote Originally Posted by Catherine Coleltt View Post
    Bernie
    I did as you said but i got the same answer

    I keep trying to show you the spreadsheet but it says it's too many characters
    (I'm new to this forum stuff can you tell!)
    BERNIE!! IGNORE THE ABOVE....IT IS THERE IT WAS IN THE CELL BELOW!!! IS THERE ANY WAY TO PUT IT IN THE SAME CELL SIDE BY SIDE

  6. #6
    Acolyte Bernie's Avatar
    Join Date
    Feb 2018
    Location
    Suburban NY
    Posts
    33
    Articles
    0
    Excel Version
    Excel 2016 (Win)
    Could you post a small example of what you have and what you want? Create a clip of your screen, showing the row and column labels and you data, and indicate what you want. Otherwise, I'm flying blind.
    Bernie Deitrick
    MS Excel MVP 2001-2010

  7. #7
    Acolyte Bernie's Avatar
    Join Date
    Feb 2018
    Location
    Suburban NY
    Posts
    33
    Articles
    0
    Excel Version
    Excel 2016 (Win)
    If you want both values in one cell, you need to use a formula like this, entered using Ctrl-Shift-Enter

    =IFERROR(INDEX($2:$2,SMALL(IF($B3:$E3=MAX($B3:$E3),COLUMN($B3:$E3)),1)),"") & IFERROR(", " & INDEX($2:$2,SMALL(IF($B3:$E3=MAX($B3:$E3),COLUMN($B3:$E3)),2)),"")
    Bernie Deitrick
    MS Excel MVP 2001-2010

  8. #8
    Seeker Catherine Coleltt's Avatar
    Join Date
    Sep 2020
    Location
    United Kingdom
    Posts
    9
    Articles
    0
    Excel Version
    2019 & Google Sheets
    That's it!! You've done it! Thank you so so much!!

  9. #9
    Seeker Catherine Coleltt's Avatar
    Join Date
    Sep 2020
    Location
    United Kingdom
    Posts
    9
    Articles
    0
    Excel Version
    2019 & Google Sheets
    Bernie.

    What if all the values were the same and I want to include all 4 cells in the final answer??

  10. #10
    Acolyte Bernie's Avatar
    Join Date
    Feb 2018
    Location
    Suburban NY
    Posts
    33
    Articles
    0
    Excel Version
    Excel 2016 (Win)
    Array enter:

    =IFERROR(INDEX($2:$2,SMALL(IF($B3:$E3=MAX($B3:$E3),COLUMN($B3:$E3)),1)),"") &
    IFERROR(", " & INDEX($2:$2,SMALL(IF($B3:$E3=MAX($B3:$E3),COLUMN($B3:$E3)),2)),"") & IFERROR(", " & INDEX($2:$2,SMALL(IF($B3:$E3=MAX($B3:$E3),COLUMN($B3:$E3)),3)),"") & IFERROR(", " & INDEX($2:$2,SMALL(IF($B3:$E3=MAX($B3:$E3),COLUMN($B3:$E3)),4)),"")
    Bernie Deitrick
    MS Excel MVP 2001-2010

Page 1 of 2 1 2 LastLast

Posting Permissions

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