0

I have a sheet with a large amount of data where each row has a title for a project (F6:F244) and then in a column a bit further along it has the Total Hours spent on each project (V6:V244) along the same row. Is it possible to find the 5 largest values from the Total Hours column and show them in another part of the sheet with their respective project in an adjacent cell?

So far I have been able to get a single project by finding the maximum value in the column by using: =INDEX($F$6:$F$244,MATCH(LARGE($V$6:$V$244,1),$V$6:$V$244,0))

Any help would be appreciated!

Tom
  • 89
  • 1
  • 2
  • 10

2 Answers2

2

I assume the top 5 values should go in 5 different cells. In this case why not use the same formula you already had, but look for 2nd, 3rd, 4th and 5th highest?

=INDEX($F$6:$F$244,MATCH(LARGE($V$6:$V$244,1),$V$6:$V$244,0))
=INDEX($F$6:$F$244,MATCH(LARGE($V$6:$V$244,2),$V$6:$V$244,0))
=INDEX($F$6:$F$244,MATCH(LARGE($V$6:$V$244,3),$V$6:$V$244,0))
=INDEX($F$6:$F$244,MATCH(LARGE($V$6:$V$244,4),$V$6:$V$244,0))
=INDEX($F$6:$F$244,MATCH(LARGE($V$6:$V$244,5),$V$6:$V$244,0))
Jorvik77
  • 342
  • 1
  • 15
  • I didn't truly understand how the formula worked so thanks for the help! It works perfectly and i'm just using a separate formula to get the highest values in the adjacent cells. – Tom May 15 '17 at 16:31
0

Just enter the below formula, wherever you want the output to be,

=INDEX($F$6:$F$244,MATCH(LARGE($V$6:$V$244,ROW(1:1)),$V$6:$V$244,0))

and drag it to 5 cells for getting the maximum 5 values. If you want the same for maximum 10, drag it for 10 rows. Hope this helps.

Gowtham Shiva
  • 3,802
  • 2
  • 11
  • 27