Pick Price From Contract Info

E

E.Q.

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:
Contract Item
StartDate
EndDate
Unit Price
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
 
S

Suleman Peerzade

please use validation if the types are not more along with vlookup.
Or else you can simply us vlookup
 
M

Max

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)*(Contracts!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

E.Q.

That worked. I'll keep this in my repetoire for sure.
But I'm trying to figure it out. Is the first 1 in the match statement
basically to identify when the three conditions that follow are true?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top