Fetch Nth last item from a list

**SDruley** · 12-31-2012, 10:22 PM

Rick, regarding your famous equation...

Code:

=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

Originally Posted by SDruley

Rick, regarding your famous equation...

Code:

=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

Originally Posted by Rick Rothstein

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