Return Last 5 Entries from an Issue List. [SOLVED]

[gumbles]

Hello everyone, I am an avid reader of this site but this is my first forum question so apologies if I make any mistakes.


I am trying to return the 5 most recent closed issues from an issue list. I am using a helper column to identify the closed issues and their position in the table. I have been experimenting with Chandoos examples on this site, using the INDEX function, but havent managed to get a working formula.


Any help you could offer would be grately appreciated.

 

[vasim]

Assuming you are using dates to identify your most recent completed issues, you can use =LARGE(range,1), =LARGE(range,2)....so on to find out the latest 5 issues closed.

 

[gumbles]

I had no idea there was a large function, so thankyou very much for your advice.


My problem now is that the LARGE function will return the last number of the helper column. I need to turn this into a reference so it will return the issue description on the same row.


Really appreciate the help.

 

[shrivallabha]

You've not posted your data layout so assuming:

Helper column is B1:B10 [All large nums are unique]

Data to fetch from is A1:A10


Formula shall be entered by combination of keys Control + Shift + Enter as it is array formula:


=INDEX($A$1:$A$10,MIN(IF($B$1:$B$10=LARGE($B$1:$B$10,ROWS($A$1:A1)),ROW($B$1:$B$10))))

 

[gumbles]

@Shrivallabha


Apologies, My Issue descriptions are C5:C35, my dates are F5:F35 and helper in K5:K35.


I have used your formula but im getting #VALUE returned. C5:C35 (text), K5:K35 (unique numbers)


Am i right in thinking that because im using dates that the helper column is redundant? The reason i started with it is because they were in the examples i looked at.


If you agree, then I need a formula that will return what row the highest date is on. Then add it to the index function.


Something like =INDEX(C5:C35, row reference for the cell that matches LARGE(F5:F35,1),)

 

[shrivallabha]

Try {Array Entered}:

=INDEX($C$1:$C$35,MIN(IF($K$5:$K$35=LARGE($K$5:$K$35,ROWS($A$1:A1)),ROW($K$5:$K$35))))

 

[gumbles]

I beleive I've done it!


I was unsure how it would handle duplicate dates but it works perfectly. The MATCH function compares the the largest date with the row which is what i needed. the Q column is the table where I have displayed my top 5 Issues.


This is what Ive come up with.


=INDEX($C$5:$C$40,MATCH(LARGE($F$5:$F$41,ROW()-ROW($Q$2)),$F$5:$F$40,0),)


Thanks to everyone who helped. Best Excel Help Ever!