Lookup a value between 2 dates

[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

 

[Auric__]

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.

No need to post your question twice.

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

 

[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.

No need to post your question twice.

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

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

hgl

 

[Claus Busch]

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.

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