Fetch Nth last item from a list

[SDruley]

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]

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]

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