1

I have the following Excel spreadsheet:

   A   B   C    D     E       F      G     H      I     J    K     L
1
2
3
4              600    150    80    600      0     0          4    
5              200    150    80     80      0     0          4
6
7

In Range K4:K5 I currently use the formula from this question to identify the last non-empty cell within the range and get the the column number of it back:

K4 = LOOKUP(2,1/(D4:J4<>0),COLUMN(D4:J4)-MIN(COLUMN(D4:J4))+1)
K5 = LOOKUP(2,1/(D5:J5<>0),COLUMN(D5:J5)-MIN(COLUMN(D5:J5))+1)

This formula works in the simple example above. However, once I use this formula in a bigger spreadsheet I get a lot of performance issues and numbers are not always updated correctly.

Therefore, I am wondering if there is alternative formula to get the column number of the last non-empty cell no matter if the values are unique or not?

Michi
  • 4,663
  • 6
  • 33
  • 83
  • 2
    Try this, I do not know if it is quicker than LOOKUP: `=AGGREGATE(14,7,(COLUMN(D4:J4)-MIN(COLUMN(D4:J4))+1)/(D4:J4>0),1)` – Scott Craner Sep 25 '19 at 13:40
  • Thanks Scott for this alternative solution. What about if I want to extract the value of the last non empty cell instead of the column number. Is there an easier solution? – Michi Sep 25 '19 at 14:03
  • 1
    use INDEX(): `=INDEX(D4:J4,AGGREGATE(14,7,(COLUMN(D4:J4)-MIN(COLUMN(D4:J4))+1)/(D4:J4>0),1)` but we can simplify it to `=INDEX(A4:J4,AGGREGATE(14,7,COLUMN(D4:J4)/(D4:J4>0),1)` – Scott Craner Sep 25 '19 at 14:06
  • Did the aggregate work for you? Just curious if it resolved quicker than the Lookup. – Scott Craner Sep 25 '19 at 19:30
  • It worked a bit quicker but no significantly. However,it is a huge file and a lot of rows to which I apply the formula so I am not sure if this evaluation is applicable for all cases. – Michi Sep 26 '19 at 09:24
  • Do you mean truly empty cells? or are values 0? And would you have 0s within the range, like> `10,20,30,0,10,0,0`? – JvdV Sep 26 '19 at 10:14
  • All values are 0. – Michi Sep 26 '19 at 10:33
  • And about my other question? – JvdV Sep 26 '19 at 12:25
  • There can also be 0s in the range. – Michi Sep 26 '19 at 12:27
  • 1
    In that case you can use any of the answers I gave you [here](https://stackoverflow.com/a/58114629/9758194). Just change the criteria to: `D3:I3>0`, or whichever range you are using – JvdV Sep 26 '19 at 15:34

1 Answers1

0

As already described in the comments below the question the following solutions are available:

Option A)
If you want to get column number of the last non-empty cell:

=AGGREGATE(14,7,(COLUMN(D4:J4)-MIN(COLUMN(D4:J4))+1)/(D4:J4>0),1)

Option B)
If you want go tet the value in the last non-empty cell:

=INDEX(D4:J4,AGGREGATE(14,7,(COLUMN(D4:J4)-MIN(COLUMN(D4:J4))+1)/(D4:J4>0),1)
=INDEX(A4:J4,AGGREGATE(14,7,COLUMN(D4:J4)/(D4:J4>0),1)
Michi
  • 4,663
  • 6
  • 33
  • 83