Last TEXT entry, excluding ""

[Brian from Maui]

I've been using =INDEX(A:A,MATCH(REPT("z",90),A:A)) to find the last text entry. Now column A has a IF filter that returns a blank(""). I'd like the last text entry excluding the "".

 

[Brian from Maui]

A non-array formula and less volatile!..... ray:

Now if I can just remember what spreadsheet I'm using this in, I'm all set.

 

[shafali]

hi,
I tried
=lookup(BigNum,A:A) to find the last non-blank integer, but it's not working.
I think this is because my column contains zeroes instead of 1. so do I have to use the fancy formula that was used for text, or is there some way around.
Thanks

 

[Brian from Maui]

hi,
I tried
=lookup(BigNum,A:A) to find the last non-blank integer, but it's not working.
I think this is because my column contains zeroes instead of 1. so do I have to use the fancy formula that was used for text, or is there some way around.
Thanks

What's not working?

 

[Aladin Akyurek]

...
I tried
=lookup(BigNum,A:A) to find the last non-blank integer, but it's not working.
I think this is because my column contains zeroes instead of 1. so do I have to use the fancy formula that was used for text, or is there some way around.
...

1. To retrieve the last non-zero numerical value from a numeric range...

C1:

=MATCH(9.99999999999999E+307,A:A)

=LOOKUP(2,1/(A2:INDEX(A:A,C1)<>0),A2:INDEX(A:A,C1))

2. To retrieve the last non-zero numerical value from a range of mixed data type...

=LOOKUP(2,1/((A2:INDEX(A:A,C1)<>0)*ISNUMBER(A2:INDEX(A:A,C1))),A2:INDEX(A:A,C1))

3. If the range is known as say A2:A12 and it's numeric...

=LOOKUP(2,1/(A2:A12<>0),A2:A12)

4. If the range is known as say A2:A12 and it's a range of mixed data type...

=LOOKUP(2,1/((A2:A12<>0)*ISNUMBER(A2:A12)),A2:A12)