Display the address of the last non-empty cell in a column (no matter if column contains values or text)

0
votes

I have the following Excel spreadsheet:

         A              B                      C
1     Product 01      Brand
2     Product 02      Brand A             Cell Reference last non-empty cell
3     Product 03      Brand A                 ??
4     Product 04      Brand B   
5     Product 05      Brand B
6     Product 06      Brand B
7

In Column B you can find the Brand for each Product. 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 go with the solution from this question:

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

However, this formula only works if Column B consists of values.

What do I have to change in the formula to always get the address of the last non-empty cell in Column B no matter if it's filled with values or text?

1
excelexcel-formula
="B"&MAX(IFERROR(MATCH(1E+99,B:B),0),IFERROR(MATCH("zzz",B:B),0)) – Scott Craner
Would you have gaps in your data? If not I think ="B"&COUNTA(B:B) would work – JvdV

1 Answers

1
votes

So what about (as per my comment):

="B"&COUNTA(B:B)

Assuming you have a dataset without gaps.