Look Up Function

[Natalie]

I have 2 columns of data, date and price. I would like a
specified cell to return the date where last price was
shown. eg. I want the cell to return "31-Oct-03". Any
formula does this?

Date Price
24-Sep-03 31.25%
30-Sep-03 29.97%
31-Oct-03 31.43%
30-Nov-03
31-Dec-03
31-Jan-04
29-Feb-04
31-Mar-04
30-Apr-04
31-May-04
30-Jun-04
31-Jul-04
31-Aug-04
30-Sep-04
31-Oct-04
30-Nov-04
31-Dec-04

 

[Vasant Nanavati]

Not fully tested, but assuming the columns are A and B:

=INDIRECT("A"&MAX(IF(NOT(ISBLANK(B1:B10000)),ROW(B1:B10000))))

entered as an array formula with <Ctrl> <Shift> <Enter>.

 

[Guest]

Suppose you got dates in column A, in the range A1:A17
And your second column of data is in column B

Now, you got "some" values in column B, but you don't know how many. But you want a formula to return the value in column A which is next to the last non-empty cell in column B

Try this formula
=INDEX(A1:A17,COUNTA(B1:B17)


----- Natalie wrote: ----

I have 2 columns of data, date and price. I would like a
specified cell to return the date where last price was
shown. eg. I want the cell to return "31-Oct-03". Any
formula does this?

Date Pric
24-Sep-03 31.25
30-Sep-03 29.97
31-Oct-03 31.43
30-Nov-03
31-Dec-03
31-Jan-04
29-Feb-04
31-Mar-04
30-Apr-04
31-May-04
30-Jun-04
31-Jul-04
31-Aug-04
30-Sep-04
31-Oct-04
30-Nov-04
31-Dec-04

 

[Natalie]

it works thanks

-----Original Message-----
Suppose you got dates in column A, in the range A1:A17.
And your second column of data is in column B.

Now, you got "some" values in column B, but you don't

know how many. But you want a formula to return the value
in column A which is next to the last non-empty cell in
column B.