In sheet: Contracts, you have in cols A to D, as posted:
Contract Item
StartDate
EndDate
Unit Price

In your other sheet to track item7, assume you have in cols A to C:
invoice dates, units purchased, and unit prices

In this sheet, to extract the unit prices into col C, you could use this
multi-criteria index n match below to lookup "item7" and check the invoice
date in col A for the appropriate valid contract period in "Contracts"

Put in C2, normal ENTER:
=INDEX(Contracts!D$2:D$10,MATCH(1,INDEX((Contracts !A$2:A$10="item7")*(Contracts!B$2:B$10<=A2)*(Contr acts!C$2:C$10=A2),),0))
Copy down as required. Adapt the ranges to suit your actuals in "Contracts".
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"E.Q." wrote:
I'd like to set up sheet showing data from a number of contracts and have
formulas on worksheets tracking individual items refer to the "contracts"
worksheet. I'd like to have the "contracts" worksheet containing the
following headings:

I'd like to have Excel pick up the proper price when given a date and the
contract item. For example, on the sheet to track item7, I would have columns
with invoice date, units purchased, and unit price. I'd like a formula in
the unit price column to pick it out of the contracts worksheet based on the
invoice date being in the term of the contract and the contract item would be
item7 in this case.
Any given date will have multiple contracts (nine, actually). But only one
for each item.
Any thoughts?
Thank you.
EQC