How to find last item in column for the dropdown list

[jaryszek]

Hi,

i am using this formula:

=OFFSET(A:A,1,0,COUNTA(A:A)-1,1) to find last item in column.

Problem is that i have blank like here:
Null
Value
Value

and countA does not include empty here so my list is shorter.

Screenshot_29.png

do you know how to solve the issue?

Jacek

[mehmetcik]

To find last row of text try: =match("zzzzz",CA1:CA200)
To find last row of text try: =match(9^99,CA1:CA200)

To find last row text or Number
=IF(ISERROR(MATCH(9.999999E+306,CA1:CA200)),MATCH("*",CA1:CA200,-1),
IF(ISERROR(MATCH("*",CA1:CA200,-1)),MATCH(9.999999E+306,CA1:CA200),
MAX(MATCH(9.999999E+306,CA1:CA200),MATCH("*",CA1:CA200,-1))))

[davsth]

perhaps
=OFFSET(A1,1,0,LOOKUP(1,1/(A2:A20<>""),ROW(A2:A20)-1),1) make the 20 as large as you need it to be

[jaryszek]

Thank you Guys,

Please Login or Register  to view this content.

This is formula is so big, can not be different?

Please Login or Register  to view this content.

this is better but i have to do this on whole column.

Best,
Jacek

[KOKOSEK]

edit: deleted
Do you want to get last row number or value from last row?

Please Login or Register  to view this content.

Usage:

Please Login or Register  to view this content.

[jaryszek]

Hi Janusz,

i am attaching example.

=OFFSET(Sheet1!$A$2;0;0;COUNTA(Sheet1!A:A)-1;1)

and you can see that result is {0,"TableA"} instead of "","TableA","TableB").

So i want to include blank here and replace 0 to "" to have list with 3 items including empty.

Jacek

[davsth]

I agree with the above

OFFSET(A:A,1,0,COUNTA(A:A)-1,1) suggests a range as the height attribute is completed

=OFFSET(A1,1,0,LOOKUP(1,1/(A2:A20<>""),ROW(A2:A20)-1),1) would give the last row number

LOOKUP(1,1/(A2:A20<>""),A2:A20) would give the last value as would

LOOKUP(1,1/(A:A<>""),A:A)if you really need the full column

[KOKOSEK]

Janusz,?

Check attached file.

[jaryszek]

Thank you!

working like a charm,

already written by mehmetcik :

MATCH("ZZZZZ";A:A;1)

Why this is looking for last row? How this is workinf? I do not have "ZZZZ" string anywhere?

Jacek

[mehmetcik]

To find last row of text try: =match("zzzzz",A:A)
To find last row of numbers try: =match(9^99,A:A)

[jaryszek]

Ok how this is working?

Jacek

[KOKOSEK]

I do not have "ZZZZ" string anywhere?

But by MATCH excel wants to find it and goes into the end. Read about how MATCH function works (last parameter!).

[jaryszek]

Thanks!

There is:

MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

So the largest value in column is the last item. But this item is not equal or not even less than "ZZZZ"...

Jacek

[KOKOSEK]

Every words is less than ZZZZ.

[jaryszek]

aaa ok got it!

Thank you !

Jacek