Lookup a value between 2 dates

  • Thread starter Thread starter Frank
  • Start date Start date
F

Frank

To keep track of frequent price changes with several products I have
LIST1 with 3 fields:
'ProductID’, 'DateOfChange’, 'NewPrice'.
Furthermore, LIST2 stores all orders with also 3 fields: 'ProductID',
'OrderDate’, 'QuantitySold’.
Here I need a 4th field, ‘TurnOver', calculated as: [LIST1!'NewPrice']
* [LIST2!'QuantitySold'].
But which formula can find the applicable ‘NewPrice’ valid at
‘OrderDate’ ?

Any assistance is highly appreciated, thank you in advance.

Kind regards,

H.G. Lamy
 
Frank said:
To keep track of frequent price changes with several products I have
LIST1 with 3 fields:
'ProductID’, 'DateOfChange’, 'NewPrice'.
Furthermore, LIST2 stores all orders with also 3 fields: 'ProductID',
'OrderDate’, 'QuantitySold’.
Here I need a 4th field, ‘TurnOver', calculated as: [LIST1!'NewPrice']
* [LIST2!'QuantitySold'].
But which formula can find the applicable ‘NewPrice’ valid at
‘OrderDate’ ?

Any assistance is highly appreciated, thank you in advance.
Click to expand...

No need to post your question twice.

I'd perhaps look into the LOOKUP function. It *might* serve your needs.
 
Frank said:
To keep track of frequent price changes with several products I have
LIST1 with 3 fields:
'ProductID’, 'DateOfChange’, 'NewPrice'.
Furthermore, LIST2 stores all orders with also 3 fields: 'ProductID',
'OrderDate’, 'QuantitySold’.
Here I need a 4th field, ‘TurnOver', calculated as: [LIST1!'NewPrice']
* [LIST2!'QuantitySold'].
But which formula can find the applicable ‘NewPrice’ valid at
‘OrderDate’ ?
Click to expand...
Any assistance is highly appreciated, thank you in advance.
Click to expand...

No need to post your question twice.

I'd perhaps look into the LOOKUP function. It *might* serve your needs.
Click to expand...

A simple LOOKUP will not identify a value between 2 dates,
unfortunately.

hgl
 
Hi Frank,

Am Fri, 23 Mar 2012 02:26:53 -0700 (PDT) schrieb Frank:
A simple LOOKUP will not identify a value between 2 dates,
unfortunately.
Click to expand...

your searched ProductID in F1 and the OrderDate in G1. Then you find the
price with:
=INDEX(List1!C:C;VERGLEICH(1;(List1!A1:A100=F1)*(List1!B1:B100<=G1);0))
The formula is an array-formula to enter with CTRL+Shift+Enter.

Warum schreibst du nicht in den deutschen Newsgroups?
microsoft.public.de.excel
de.comp.office-pakete.ms-office.excel


Regards
Claus Busch
 
Back
Top