6
votes

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.

2
If your values are unique you could use MATCHTim Edwards
What is the last cell filled with text or number?Scott Craner
If I knew the value or the last cell filled I wouldnt need this function.Spartacus Rocha

2 Answers

6
votes

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.

0
votes

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