Find top and bottom 5 companies

[EuroGorilla]

Hi everyone, longtime lurker over here. I found this forum extremely helpful in the past so was hoping I could get some help on the problem I've recently encountered!

I have a worksheet with 14 tabs each tracking approximately 150 companies and their financial performance in the form of indicators. In the cover page where I aggregate the information from all these worksheets I can't seem to find a way for excel to grab the top and bottom performers names in my top 5 aggregate table.

So I figured a way for excel to grab the top 5 and bottom 5 performers across all 2,100 companies based on one indicator I'm interested in. I used a very helpful function I found on this forum [=LARGE(CHOOSE({1,2,3,4,5,6,7,8,9,10,11,12,13,14},Spreadsheet1!$12:$BL$150,Spreadsheet2!$BL$12:$BL$150,etc.),1); The problem is this function only grabs the value of of that financial indicator (top 5 and bottom 5) but what I want is for excel in the column right next to the value of the top and bottom indicators to generate the name of the company that indicator belongs to (which can be found in the same row across those tabs) and in another column to the left I would like excel to generate the name of the spreadsheet where the top 5 and bottom 5 companies can be found.

So in effect I would need 2 functions. One that will allow me to search for the names of the companies that match the top 5 and bottom 5 indicators already generated across all 14 worksheets, and second function that would match the name of the company to the worksheet that it can be found on.

I've spent half a day on this already and nothing seems to work so if anyone would have a helpful solution I would be eternally grateful!

Thanks!

[Pete_UK]

It would take a lot of work to set something up to simulate what you have described above, before being able to try a few things out. Post a sample workbook and you might get more willing helpers.

Hope this helps.

Pete

[EuroGorilla]

Pete good suggestion. Please see attached spreadsheet and let me know if you have any further clarifying questions. I've cleaned the entire document only leaving the essential entries. Total Shareholder's Return for the last week and all the companies I'm looking at. So the functions I'm after would ideally be located on the overview tab of the attached. First one would match the top 5 and bottom 5 TSR indicators with the company name and the second function would then indicate in which tab can this company be found.

Any help or suggestions would be much appreciated! I'm stuck and since this is the last missing piece of the puzzle I can't move on.

Thanks guys!