Display the address of the last non-empty cell in a column (if values are not unique)

0
votes

I have the following Excel spreadsheet:

       A          B                C
1     Month     Profit      
2     Jan.        100             Cell Reference last non-empty cell
3     Feb.        200             ??
4     Mar.        300
5     Apr.        300
6
7

In Column B you can find the profit for each month. In Cell C3 I want to to display the address of the last non-empty cell in Column B. In this case it would be B5. For this I tried to modify the formular from this question:

=ADDRESS(COLUMN(B2),MAX(IFERROR(MATCH(1E+99,$B2:$B5),0),IFERROR(MATCH("zzz",$B2:$B5),0)),4,1)

However, I could not make it work.

What do I have to change in my formula to always get the address of the last non-empty cell in Column B no matter if it is unique or not?

1
excelexcel-formula

1 Answers

1
votes

Use:

="B"&MATCH(1E+99,B:B)