Sorry, the formula was insufficiently tested it seems ..
Perhaps better to amend the array formula in Sheet2's C3 to:
=IF(OR(A3="",B3=""),"",INDEX(Sheet1!$D$3:$D$100,MA TCH(1,(YEAR(Sheet1!$A$3:$A
$100)=YEAR(A3))*(Sheet1!$A$3:$A$100<A3)*(Sheet1!$B $3:$B$100=A3)*(Sheet1!$C$
3:$C$100=B3),0)))
Added one more criteria to help ensure unique matching
to suit the source data format:
(YEAR(Sheet1!$A$3:$A$100)=YEAR(A3))
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
<dom wrote in message ...
Thank you for your help, it seem function one of part. I also prefer when
I
change sheet2 "A2" value to 3.2.2001, the sheet2 "C3" value will be
changed
to "$300"
thanks
dom
|