Usually, you need to return the last or first item in a list, but every once in a while you may find yourself needing to return a different "to last" result in a list. These formulas will help.
Contents:
Formulas
For both of these formulas, the "n" in each formula should be replaced by the nth position from the last item you wish to return. For example, n as 1 would return the last item, 2 the second to last, 3 the third to last, and so on.
The "range" should be replaced with the vertical or horizontal list that you want to evaluate. For example, for a 100 item list in column A, you could use "A1:A100" for your range.
Full column or row reference also work, "A:A" or "2:2" will also return the correct value.
Return the nth to Last item in a Vertical List (Column):
= INDEX(range, MATCH(n + 1, 1/(range <> ""), 1) - n + 1)Return the nth to Last item in a Horizontal List (Row):
= INDEX(range, MATCH(n, 1/(range <> ""), 1) - n + 1)Examples
How to Return the nth to Last Non-blank Item in a Column
If we have a list of items in the range C3:C12, we can use the following formula to return the nth to last item from that list. The only thing that would need to be changed is the range you're evaluating and the place that you would like to return.
In this formula G5 determines which place is returned. In this example, we have 2 put in G5 which tells the formula to take the second to last item from our list.
If you wanted to return the third to last item from this list, then a 3 input here would return the correct item.
It is important to note that this formula will ignore all blanks, only returning the last non-blank item.
= INDEX(C3:C12, MATCH(G5 + 1, 1/(C3:C12 <> ""), 1) - G5 +1)
How to Return the nth to Last Non-blank Item in a Row
We can use a similar formula if we have a horizontal list. The following formula will return the nth to last item from a row. The only thing that would need to be changed is the range you're evaluating and the place that you would like to return.
In this formula D3 determines which place is returned. In this example, we have 2 put in D3 which tells the formula to take the second to last item from our list.
If you wanted to return the third to last item from this list, then a 3 input here would return the correct item.
= INDEX(C5:L5, MATCH(D3, 1/(C5:L5 <> ""), 1) - D3 + 1)