# Thread: This ones a cracker! (2000/03)

1. ## This ones a cracker! (2000/03)

I have used this formula: =LOOKUP(9.99999999999999E+307,B13:B438) to find and display the last cell entry in (in this case) column B. Column A has the days of the year from 1 Jan to 31 Dec 05.

Is there a formula or array, that will give the date opposite the last cell entry in column B?(or C, D E...etc). I've tried a VLOOKUP and putting another column of dates on the right side of the info, MATCH, INDEX, etc, but I'm guessing this one will be a special one.

Thank you

2. ## Re: This ones a cracker! (2000/03)

This finds the last entry in Column A

and this the last in Column B

Obviously change the range to suit your needs

3. ## Re: This ones a cracker! (2000/03)

Try
<code>
=INDEX(A13:A438,MATCH(9.99999999999999E+307,B13:B4 38))
</code>
BTW why B13:B438? It contains426 cells, not 365...

4. ## Re: This ones a cracker! (2000/03)

You can compine the Offset and Match functions for this one:

=OFFSET(B3,MATCH(LOOKUP(9.99999999999999E+307,B13: B438),B13:B438)-1,<font color=red>1</font color=red>,1,1)

Change the <font color=red>red 1</font color=red> to the number of columns to the right of column B you want returned.

5. ## Re: This ones a cracker! (2000/03)

Hans

I went back 2 months in addition to the 12 months to test for other conditions in the sheet. I can send the sheet if you are curious.

Now to work on the suggestions!!!

6. ## Re: This ones a cracker! (2000/03)

Jezza,
That works for finding the last populated cell in the column, which for column A, was 31-12-05 and for column B was 0:00 which is a time cell in the format [h]:mm. I was looking for the last entry in B and the date in A which corresponded. But thanks for taking the time and showing us all an interesting formula.

Mbarron,
I have puzzled over this one, and have not grasped the way it works; (then again, grasped never was my strong point!) but, thank you for taking the time to reply and your knowledge.

Hans,
That's the one! It worked! Just made the A column ref absolute and it does what it says on the tin. (AKA WOPR).

Many thanks everyone.

#### Posting Permissions

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