PDA

View Full Version : Fetch Nth last item from a list


SDruley
12-31-2012, 10:22 PM
Rick, regarding your famous equation...


=LOOKUP(2,1/(CG5:CG1540<>""),CG5:CG1540)

How can i modify it to tell me what the second and third to the last items are in the given column?

Steve
Rick Rothstein
12-31-2012, 11:30 PM
Rick, regarding your famous equation...


=LOOKUP(2,1/(CG5:CG1540<>""),CG5:CG1540)

How can i modify it to tell me what the second and third to the last items are in the given column?

Steve

I don't think that formula can be modified to do that; however, you should be able to use this formula where you would specify the number of the item you want (1 for last, 2 for next-to-last, 3 for third-from-last, etc.) where I have highlighted in red...

=INDEX(CG1:CG1540,SUMPRODUCT(LARGE((CG5:CG1540<>"")*ROW(CG5:CG1540),3)))
SDruley
01-01-2013, 09:28 AM
I don't think that formula can be modified to do that; however, you should be able to use this formula where you would specify the number of the item you want (1 for last, 2 for next-to-last, 3 for third-from-last, etc.) where I have highlighted in red...

=INDEX(CG1:CG1540,SUMPRODUCT(LARGE((CG5:CG1540<>"")*ROW(CG5:CG1540),3)))

WOW. This is wonderful

Steve