Please help

Catherine Coleltt

New member
Joined
Sep 14, 2020
Messages
9
Reaction score
0
Points
0
Location
United Kingdom
Excel Version(s)
2019 & Google Sheets
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), B3:D3, 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
 
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
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!)
 
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.
 

Attachments

  • Example for Catherine.xlsx
    10.1 KB · Views: 2
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:eek:hwell:
 
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.
 
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.

What if all the values were the same and I want to include all 4 cells in the final answer??
 
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)),"")
 
Nearly there!

Bernie thanks for all your help on this.

It's now pulling out 4 names if needed.Is there a way to hide the names at the beginning.
When the Spreadsheet is empty it's putting all the names in anyway and then changing them once the data in cells B-E are entered.

I ideally need column F blank so that when the points are entered it then populates the column F with the winning teams of the week, because otherwise the main Totals sheet has all the teams on for every week in advance (and that will just confuse the Head teacher)

I think I've managed to upload the spreadsheet so you can see what I'm trying to achieve.

Catherine
 

Attachments

  • HOUSEPOINTS 20_21.xlsx
    362.6 KB · Views: 5
Last edited:
For Year 6, cell F3, array-enter using Ctrl-Shift-Enter:

=IF(COUNTA(B3:E3)=0,"",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)),""))
 
Here is a completed version with the summary tab filled out.
 

Attachments

  • HOUSEPOINTS 20_21.xlsx
    485.2 KB · Views: 6
OMG Bernie

I'm sitting here with tears. I didn't know that people like you still existed. You have saved me hours of work! I'm so very very grateful. I can now get on a do what I am good at, (teaching) without fretting that I haven't delivered. I'm going to look at what you've done and figure it out, so I can do it again and again.
Your a superstar!:cheer2:
 
I'm very happy to hear that my version does what you need. People like me exist all over these forums - I've been on the receiving end (and still am!) so I like to pay back those kindnesses. I especially like helping those who make contributions to their communities: teachers, firefighters, nurses and doctors....

Take care, and good luck with the new school year and the extra new challenges that all teachers are facing.
 
Sorry. Back again!
We started to use the Spreadsheet and it looks great but when its the 18th of anything it populates all the 18ths. Is there a way to not do this?
 

Attachments

  • HOUSEPOINTS 20_21_Master.xlsx
    139.2 KB · Views: 6
I changed how the lookup is done - this will just pull them in order of entry...
 

Attachments

  • HOUSEPOINTS 20_21.xlsx
    485.4 KB · Views: 6
Catherine: next time you start a thread here, please follow the forum rules and give it a descriptive title. "Please Help" gives us no clue what the query is about or whether we, as individuals, might be able to help. Please help us to help you. Thanks.
 
Back
Top