8

I need to find the last non empty cell in an Excel spreadsheet, but I need its address, not its value.

For example: When I want the value for the last non empty cell in column K, I use this formula:

=LOOKUP(2;1/(NOT(ISBLANK(K:K)));K:K)

My problem is, I dont want cell's value, I need its address instead.

I already tried to use function CELL without success.

Spartacus Rocha
  • 546
  • 1
  • 6
  • 14

2 Answers2

7

You can get the last populated row number of a known column with the MATCH function but it helps to know whether the column is filled with numbers and/or dates or text.

=match("zzz"; K:K)   'for last text¹ in column K
=match(1e99; K:K)    'for last date or number in column K

The ADDRESS function returns the rest.

=address(match("zzz"; K:K); 11; 4; 1)  'address of last text¹ in column K
=address(match(1e99; K:K); 11; 4; 1)   'address of last date or number in column K
=address(max(iferror(match(1e99; K:K); 0); iferror(match("zzz"; K:K); 0)); 11; 4; 1)   'address of last date or number or text¹ in column K

The 4 is for relative addressing (e.g. K99); the 1 is for xlA1 addressing. See the provided link for full syntax on the ADDRESS function.

¹ Note that a 'zero-length string' (e.g. "" ) like that typically returned by an error controlled MATCH or VLOOKUP is considered a text value, not a blank or null value.

  • Perhaps it should be pointed out to readers of this post that the given construction based on text entries may fail if there are null strings present within the column being queried, if only because to some of those readers it may not be clear from the OP's definition of "empty" that such strings are not in fact present. – XOR LX Jul 08 '16 at 15:34
  • @XORLX - I'm not sure I understand. If there are blank cells before the last populated cell, the formulas will still find the last populated cell (date/number or text). –  Jul 08 '16 at 15:36
  • What if there may be null strings ("") beyond the last (i.e. desired) text entry? – XOR LX Jul 08 '16 at 15:37
  • @XORLX - Yes, that would affect the result. My apologies, I've always referred to those as zero-length strings (which *are* a text value, not null or truly blank). –  Jul 08 '16 at 15:41
  • @Jeeped And I for one can appreciate your (more precise) definition! Others might not, however; hence my comment. Cheers! – XOR LX Jul 08 '16 at 17:03
  • @XORLX - Fair enough; caveat added to answer. –  Jul 08 '16 at 21:38
0

For reference, a variation of your original formula will do the trick too:

="K"&MATCH(2,1/(K:K<>""),1)

In the below example, it functions for text (B:B), date (A:A), numbers (D:D) with blank cells in between:

enter image description here

Marcelo Scofano Diniz
  • 590
  • 1
  • 7
  • 24