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

3
votes

I have the following Excel spreadsheet:

             A                             B       C      D      E       F

1 |                                     |  Jan.   Feb.   March   April   May 
2 | Profit                              |   100    200    200    
3 | Cell Reference last non-empty cell  |   ??

In Row 2 you can find the profit for each month. In Cell B3 I want to to display the address of the last non-empty cell in Row 2. For this I tried the following formula:

=ADDRESS(ROW(B2),MATCH((IFERROR(LOOKUP(2,1/($A2:$F2<>0),$A2:$F2),0)),$A2:$F2,0),4,1)

This formula works perfectly if all the numbers in Row 2 are unique. However, in my example above you can see that the profit in Februay equals the profit in March; therefore, the formula above gives me the address C2 instead of D2.

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

3
excelexcel-formula
Whenever someone wants the address I always ask why? Are you going to use it in another formula to return a value? If so we can skip this step completely and it would be much easier. So is the address the final outcome? – Scott Craner
Hi Scott, in the end I will use it in another formula. I think I might post another question with the final desired outcome but so far you already helped me a lot with your answer below. – Michi
then skip the whole address and just use the MATCH part in an INDEX formula. ADDRESS is volatile and not needed in most cases. – Scott Craner

3 Answers

3
votes

Use:

=ADDRESS(ROW(B2),MATCH(1E+99,$A2:$F2),4,1)

Which will return the last cell with a number in that range.

For text and numbers:

=ADDRESS(ROW(B2),MAX(IFERROR(MATCH(1E+99,$A2:$F2),0),IFERROR(MATCH("zzz",$A2:$F2),0)),4,1)

enter image description here

3
votes

Consider:

=ADDRESS(2,MATCH(TRUE,INDEX(ISBLANK(2:2),0,0),0)-1)

enter image description here

NOTE:

  1. will handle either text or numbers, unique or not
  2. is not an array formula
  3. data must be compact....no empties in the middle

The last restriction is important because the formula relies on finding the first empty in the row and taking one step back!

1
votes

You can use the following array formula:

= ADDRESS(ROW(B2),MAX(COLUMN(2:2)*NOT(ISBLANK(2:2))))

This returns the address of the last non-empty cell in the 2nd row.

Note this is an array formula, so you muse press Ctrl+Shift+Enter on the keyboard when entering this formula rather than just pressing Enter.

I agree with @ScottCraner that returning the address usually isn't useful. If you are planning on using this value, as part of another formula, you should not do this:

= INDIRECT(ADDRESS(ROW(B2),MAX(COLUMN(2:2)*NOT(ISBLANK(2:2)))))

Instead, skip using INDIRECT and ADDRESS altogether and instead use INDEX to get the value in that cell, i.e.

= INDEX(2:2,MAX(COLUMN(2:2)*NOT(ISBLANK(2:2)))))