google-sheets

shortest formula to get the last non zero value from column

suppose i have A1:A5 like so:

|    |
| 12 |
|    |
| 14 |
|    |

i'm using this formula to get the first non zero occurence: =INDEX(A1:A5, MATCH(TRUE,INDEX(A1:A5<>0),0)), which:

  1. INDEX(A1:A5<>0) returns a column like 'FALSE TRUE FALSE TRUE FALSE'
  2. MATCH(TRUE,previousPart,0) returns 2 meaning that the second row is the the first one to match exactly TRUE
  3. INDEX(A1:A5,previousPart) returns the val from row 2

but i would like to get the last non zero occurence. in this case, 14. what is the shortest and/or simplest way to achieve this?

Solution

  • Try:

    =CHOOSEROWS(FILTER(A1:A5, 0 <> A1:A5), -1)