=OFFSET(INDEX(A:A,MATCH(C2,A:A,0)),MATCH(TRUE,A$10: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