Try this array formula**.

There *must* be at least one empty cell at the end of the range of your
data.

Assuming your data is in the range A2:A10 (A10 being the empty cell after
the last date entry for V101).

C2 = product code to lookup

Array entered** in D2:

=OFFSET(INDEX(A:A,MATCH(C2,A:A,0)),MATCH(TRUE,A$10 :INDEX(A:A,MATCH(C2,A:A,0))="",0)-2,0)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Format as Date

--
Biff
Microsoft Excel MVP


"craig" wrote in message
...
Hi, My data in one column A:A - Where V100 &V101 are product codes, dates
run
down the column and Blank is a blank cell befor each new product code.
V100
1-Sep-09
2-Sep-09
Blank
V101
1-Oct-09
5-Oct-09
10-Oct-09
Blank

Question is how do i return the date value for the last entry (eg before
the
blank) for each product code I want to find. Note also the last date entry
is
always the max value date. Eg V100 = 2-Sep and V101 =10-Oct.
Thks Craig