Display the column number of the last non-empty cell within a range (if values are not unique)

1
votes

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?

1
excelexcel-formula
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
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
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
Did the aggregate work for you? Just curious if it resolved quicker than the Lookup. – Scott Craner
In that case you can use any of the answers I gave you here. Just change the criteria to: D3:I3>0, or whichever range you are using – JvdV

1 Answers

0
votes

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)