Look up relative value when multiple conditions (between dates) met

[LifeIsGood2015]

Help please

I am trying to write a formula to link two arrays. One that contains a model# and the date of purchase, and the other that has the price ranges by models for date ranges. I am trying to write a formula that populates the price (column C) based on the model # and date of purchase.

A B C E F G H
1 Purchase Date Model Price Model Price Start Date Price End Date PRICE
2 04/06/15 77585-52 77585-52 3/1/2015 4/10/2015 $ 150.00
3 04/11/15 77585-52 77585-52 4/11/2015 4/20/2015 $ 200.00
4 04/22/15 77585-52 77585-52 4/21/2015 5/1/2015 $ 250.00
5 04/28/15 77585-52 98557-64 3/1/2015 4/10/2015 $ 125.00
6 04/06/15 98557-64 98557-64 4/11/2015 4/20/2015 $ 150.00
7 04/11/15 98557-64 98557-64 4/21/2015 5/1/2015 $ 200.00
8 04/22/15 98557-64
9 04/28/15 98557-64

 

[eibi]

Good Life,

Welcome to the forums.

The SUMPRODUCT formula is what you want.

See attached.

All best.

 

[Tiago MF]

Try the following on C3:
=SUMPRODUCT((B3>=$G$3:$G$8)*(B3<=$H$3:$H$8)*(C3=$F$3:$F$8)*$I$3:$I$8)
And drag down

Regards

 

[LifeIsGood2015]

Much gratitude eibi and Tiago MF! Sumproduct worked like a charm. I really appreciate you pointing me in that direction!

 

[Khalid NGO]

Hi,
One more with light weight SUMIFS

=SUMIFS($I$3:$I$8,$G$3:$G$8,"<="&B3,$H$3:$H$8,">="&B3,$F$3:$F$8,C3)

Regards,

 

[Khalid NGO]

another one with CSE...

=SUM(IF($G$3:$G$8<=B3,IF($H$3:$H$8>=B3,IF($F$3:$F$8=C3,$I$3:$I$8))))

enter with Ctrl+Shift+Enter