# Thread: Last Value

1. I'm trying to find the last value in a column (I've asked this before but the formula is weird in that post - has a happy face in it).
I can't seem to make it work. I entered:
=INDEX(B6:B365,MATCH(9.99999999999999E+307,B)
but get and error:
#NAME?

Any help greatly appreciated.

2. [quote name='bfxtrfcmgr' post='783547' date='08-Jul-2009 09:09']I'm trying to find the last value in a column (I've asked this before but the formula is weird in that post - has a happy face in it).
I can't seem to make it work. I entered:
=INDEX(B6:B365,MATCH(9.99999999999999E+307,B)
but get and error:
#NAME?

Any help greatly appreciated.[/quote]
What post number was that - I'm having trouble finding it.

3. Try

=INDEX(B:B,MATCH(9.99999999999999E+307,B:B))

or somewhat shorter

=LOOKUP(9.99999999999999E+307,B:B)

If you only want to look at B6:B365:

=LOOKUP(9.99999999999999E+307,B6:B365)

4. [quote name='WebGenii' post='783550' date='08-Jul-2009 08:14']What post number was that - I'm having trouble finding it.[/quote]

Hans had the answer for me (a guardian angel of mine):
=LOOKUP(9.99999999999999E+307,B6:B365)

I haven't been around much since the new format and I can't (believe it or not) find the post number - certainly it can't be "3" in the far right corner.
Little help?

5. I think you are blending two different formulas from that previous post Final Value in a Column
There were two options offered:
=LOOKUP(9.99999999999999E+307,A:A)

and I see Hans has beat me to the rest ...

6. [quote name='HansV' post='783552' date='08-Jul-2009 08:15']Try

=INDEX(B:B,MATCH(9.99999999999999E+307,B:)

or somewhat shorter

=LOOKUP(9.99999999999999E+307,B:

If you only want to look at B6:B365:

=LOOKUP(9.99999999999999E+307,B6:B365)[/quote]

Thanks friend! I opted with the last one and it works great.

7. Cool!

8. [quote name='WebGenii' post='783557' date='08-Jul-2009 08:25']I think you are blending two different formulas from that previous post Final Value in a Column
There were two options offered:
=LOOKUP(9.99999999999999E+307,A:A)

and I see Hans has beat me to the rest ...[/quote]

Hey! It happened again when I quoted Hans in my reply to him.
I leave for a few months, come back, and then break the Lounge - GREAT!

9. This version of the Lounge uses shortcuts for smileys, e.g. :) becomes and B) becomes
If you want to use one of those shortcuts as "normal" text, you have to be very careful...

10. [quote name='HansV' post='783562' date='08-Jul-2009 08:38']This version of the Lounge uses shortcuts for smileys, e.g. :) becomes and B) becomes
If you want to use one of those shortcuts as "normal" text, you have to be very careful... [/quote]

I kinda figured. . . so what's the trick? Top secret stuff?

11. You can make one of the characters bold, for example. I typed this: [b]B[/b]) to get B)

12. [quote name='HansV' post='783564' date='08-Jul-2009 08:55']You can make one of the characters bold, for example. I typed this: [b]B[/b]) to get B)[/quote]
You are always so creative. Thanks!

13. Here is a more elegant and robust formula from Dick Kusleika
{=INDEX(A:A,MAX(NOT(ISBLANK(A:A))*(ROW(A:A)),1))}

1 - it will recognize both text and numbers
2 - blanks don't bother it

Finally, I think the core of the formula {=MAX(NOT(ISBLANK(A:A))*(ROW(A:A)),1)} will be useful for finding all sorts of "last cell" variations.

14. You may have to settle with some XL versions with the array (confirm with ctrl-shift-enter):
=INDEX(A1:A65535,MAX(NOT(ISBLANK(A1:A65535))*(ROW( A1:A65535)),1))

Earlier versions did not allow using the full column in the array formula...

Steve

15. good call

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•