Fetch Nth last item from a list

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

Quote:

---

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)))

Quote:

---

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