CODEWITHSUNDEEP
excelexcel-formula
Michi
Michi

Reputation: 5471

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

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?

Upvotes: 3

Views: 2911

Answers (3)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

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!

Upvotes: 3

Scott Craner
Scott Craner

Reputation: 152505

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

Upvotes: 3

ImaginaryHuman072889
ImaginaryHuman072889

Reputation: 5185

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)))))

Upvotes: 1

Related Questions