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. 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))),"")

3. 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. 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.

5. Originally Posted by Catherine Coleltt
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. 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.

7. 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)),"")

8. That's it!! You've done it! Thank you so so much!!

9. Bernie.

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

10. 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)),"")

Page 1 of 2 1 2 Last

Posting Permissions

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