Row of Last non-blank cell in column Dynamic Named Range

[Jeffrey Mahoney]

I currently use this as part of my OFFSET function to find the last used row because it works with for both numbers and text.
MAX((AB:AB<>"")*ROW(AB:AB))

I've tried using Match(1e+300,AC:AC,1) which will find the last row if all the cells contain numbers. Also Match("zzzzzzzzzzz",AC:AC,1) which works for text.

Does anybody else use something more simple?

 

[Cubist]

I need a bit of clarification here. When you say "last row", are you referring to the actual row # of the excel spread sheet? Or the n-th row of your dynamic array. In the example below, suppose your array is from A4:A16. Would your last row be 13 or 10 (the 10th element of the dynamic array)?

 

[Jeffrey Mahoney]

In your example, I'm looking for 13. The actual row number

 

[Cubist]

How about

=ROW(XLOOKUP(TRUE,A:A<>"",A:A,,0,-1))

Don't know if that consider simpler.

 

[Tetra201]

Another approach:

=LOOKUP(2,1/(AB:AB<>""),ROW(AB:AB))

 

[Cubist]

Some variations of the above.

=MAX(MATCH(2,1/(A:A<>""),1),1)

=AGGREGATE(14,6,ROW(A:A)/(A:A<>""),1)

 

[Jeffrey Mahoney]

Thank you Cubist and Tetra,

Those are interesting variations on the same theme also using two functions. I appreciate your time. I'm going to continue using my solution for dynamic named ranges like this:
=$AC$8:INDEX($AC:$AC,MAX((AC:AC<>"")*ROW(AC:AC)))

Brown Bag Lunch Ideas.xlsm
	AC	AD
7	Clean ID	Series
8	80555021	8 Series
9	80555021	8 Series
10	522122048	5 Series
11	80517998	8 Series
12	80292682	8 Series
13	80106998	8 Series
14	38160118A	Other
15	80558675	8 Series
16	80558675	8 Series
17	602049400	6 Series
Sheet2

 

[Cubist]

You're welcome.