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

Here's how it works...

A2...V100
A3...1-Sep-09
A4...2-Sep-09
A5
A6...V101
A7...1-Oct-09
A8...5-Oct-09
A9...10-Oct-09
A10

Let's assume the lookup value is V101 in C2.

=OFFSET(INDEX(A:A,MATCH(C2,A:A,0))...

The INDEX/MATCH finds V101 in the range and returns that cell address to
OFFSET:

=OFFSET($A$6...

This defines the start of the range for the product code V101.

The information we want is located immediately above the first empty cell
for each product code. So, we need to find the first empty cell in the range
that is below the product code we're looking for. We do that with:

MATCH(TRUE,A$10:INDEX(A:A,MATCH(C3,A:A,0))="",0)

Once again we use INDEX/MATCH to find V101 in the range and that defines the
starting point for the MATCH lookup_array.

A$10:INDEX(A:A,MATCH(C3,A:A,0))

A$10:$A$6

Writing that expression as I did starting with A$10 is just my personal
preference. You can also write it like this:

INDEX(A:A,MATCH(C3,A:A,0)):A$10

Either way, Excel will evaluate it as $A$6:A$10.

Now we have the range that relates to product code V101 - A6:A10. So, we
need to find the first empty cell in that range. We do that with this:

MATCH(TRUE,$A$6:A$10="",0)

The expression:

$A$6:A$10="" will return an array of either TRUE or FALSE

If the cell is blank(empty) = TRUE, if the cell is not blank(empty) = FALSE

A6 = "" = FALSE
A7 = "" = FALSE
A8 = "" = FALSE
A9 = "" = FALSE
A10 = "" = TRUE

MATCH(TRUE,{FALSE;FALSE;FALSE;FALSE;TRUE},0)

= 5

At this point we have:

=OFFSET($A$6,5...

We need to make an offset correction which is 2 (rows) so:

=OFFSET($A$6,5-2,0)

=OFFSET($A$6,3,0)

To find the result we're looking for:

OFFSET A6 by 3 rows and 0 columns = A9

A9 = 10/10/2009

So:

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

= 10/10/2009



exp101
--
Biff
Microsoft Excel MVP


"craig" wrote in message
...
Biff, You are the Man! Perfect.

Just one last question just to help me understand the formula.. I'm across
the index, match and offset formulas generally.... but cant quite
understand
the Match formula to find the rows in the offset, specifically why is the
lookup value "TRUE" and why does the lookup array start from the bottom of
range. Cant quite grasp the whole nesting in that match formula.

Appreciate your help

"T. Valko" wrote:

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


.