This ones a cracker! (2000/03)

Topic

New Reply

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

Reply | Quote

Replies

This finds the last entry in Column A

{=INDIRECT(ADDRESS(MAX((A1:A100″”)*ROW(A1:A100)),COLUMN(A1:A100),4))}

and this the last in Column B

{=INDIRECT(ADDRESS(MAX((A1:A100″”)*ROW(A1:A100)),COLUMN(A1:A100)+1,4))}

Obviously change the range to suit your needs

Reply | Quote

Try

=INDEX(A13:A438,MATCH(9.99999999999999E+307,B13:B438))

BTW why B13:B438? It contains426 cells, not 365…

Reply | Quote

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!!!

Reply | Quote

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

=OFFSET(B3,MATCH(LOOKUP(9.99999999999999E+307,B13:B438),B13:B438)-1,1,1,1)

Change the red 1 to the number of columns to the right of column B you want returned.

Reply | Quote

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.

Reply | Quote