Vlookup Search Function

Hi all,

I am new to the boards, so be friendly

I am encountering a difficult situation when trying to complete a search on my data set. Without getting too deep into my data I have included the sample below to explain my dilemma.

Without getting too specific my data field contains the following significant information.

Item Number Price Valid Date Price Expiration Date
ABCD 100.00 1/1/2009 12/31/2009
ABCD 120.00 1/1/2010 07/31/2010
ABCD 125.00 8/1/2010 12/31/9999
EFGH 50.00 1/1/2009 12/31/9999

I would like to search another set of data by Item Number and the date of purchase to retrieve the price.

For example, I would like to know the price for item "ABCD" on 5/15/2010 by using some type of search function.

My data set is large and pricing dates are irregular which has eliminated my ability to use a vlookup and match formula combination.

Any help is appreciate.

Thanks

Assuming that the dates for a price never overlap then is works for me ... =SUMPRODUCT(B1:B4,--(A1:A4="ABCD"),--(C1:C4<=DATE(2010,5,15)),--(D1:D4>=DATE(2010,5,15)))