Index / Large Help
Hi there,
I need some help with Large and Index function.
In my spreadsheets, I have the columns that contain the team name and score and the row contain the week no.
I can locate the top score and locate the week no. but I don't how to locate the Team name.
Can you please advice what formula I should use?
Please see attached sample: Highlighted in Yellow.
Regards
Ray
Hi,
in F19 and below
Please Login or Register to view this content.
Regards
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.
Please, mark your thread [SOLVED] if you received your answer.
Hi,
attached an example.
Hi,
Thanks but I just tried both and when I have 3 the same score, the team name don't show correctly.
Team 01 Team 02 Team 03 Team 04 Team 05
Week 01 2000 1741 1931 1686 1684
Week 02 2000 2000 2000 1762 1817
Week 03 1797 1841 1990 1936 1891
Results
Top 5
Duplicate 1990
Count 5
Rank Score Week No. Team No.
1 2000 Week 01 Team 01
2 2000 Week 02 Team 01
3 2000 Week 02 Team 01
4 2000 Week 02 Team 01
5 1990 Week 03 Team 03
Hi
you could exploit AGGREGATE (available from Excel 2010)
=IFERROR(INDEX(D$5:H$5,AGGREGATE(15,6,COLUMN($D:$H)-COLUMN($D$1)+1/(INDEX($D$6:$H$8,MATCH(E19,C$6:C$8,0),0)=D19),COUNTIFS(D$19:D19,D19,F$19:F19,F19))),"")
Maybe the red segment is too much prudent...
Regards
Last edited by canapone; 03-28-2017 at 02:52 AM.
Hi Canapone,
Sorry but I can't seems to get it right. can you put that into the sample that I sent?
many Thanks
Ray
Hi,
attached an example
You are right: in the formula there was a typo...my apologies
Please Login or Register to view this content.
Hope to have a better luck...
Please refer to the file attached as I'm translating formulas from Italian settings.
Last edited by canapone; 03-29-2017 at 05:00 AM.
Hi
This will be better formula for you instead using array formula!
canapone formula is good?
But this one is shorter formula
E19Copy downFormula:
Please Login or Register to view this content.
F19Copy downFormula:Please Login or Register to view this content.
See the file in highlight yellow
Regards
To help you by my post? it would be nice to click onto say "Thank you".
If you are happy with a solution to your problem?
Click Thread Tools above your first post,
select "Mark your thread as Solved".
Thank you both micope21 & Canapone - exactly what I needed.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Hi wpm7113,
I suggest to shuffle around the numbers to test the formulas, or more simply try to write 2000 in E6 or in H8 to check outputs from formulas.
In the attachment some examples.
Last edited by canapone; 04-02-2017 at 01:49 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks