find price by date range and SKU

[mre]

I need help in populating price on "My List" table with the price from "Price Table", based on SKU and PODate between StartDate & EndDate

Price Table
_ A B C D
1 StartDate EndDate SKU Price
2 7/6/2009 2/13/2010 HW001 110.00
3 2/14/2010 3/12/2011 HW001 150.00
4 3/13/2011 4/11/2012 HW001 200.00
5 4/12/2012 5/3/2013 HW001 275.00
6 5/4/2013 5/16/2014 HW001 325.00
7 7/6/2009 2/13/2010 HW004 50.00
8 2/14/2010 3/12/2011 HW004 75.00
9 3/13/2011 4/11/2012 HW004 90.00
10 4/12/2012 5/3/2013 HW004 120.00
11 5/4/2013 5/16/2014 HW004 145.00

My List
_ A B C
1 PODate SKU Price
2 2/1/2011 HW001 ?
3 4/30/2012 HW001 ?
4 3/11/2011 HW004 ?
5 3/12/2014 HW004 ?

So, C2 on "My List" should be $150.00. C5 would be $145.00

Any suggestions how to do this?

Thank you in advance. Greatly appreciate it.

 

[Somendra Misra]

Hi mre,

Try below array formula:

=INDEX($D$2:$D$11,MATCH(1,(B14=$C$2:$C$11)*(A14>=$A$2:$A$11)*(A14<=$B$2:$B$11),0))

Here Price Table is in A1:D11 with headers in row 1.
My list range is A13:C17 with header in row 13.

Put the formula in C14 and copy down. Enter with Ctrl+Shift+Enter.

Regards,

 

[mre]

Works like a charm.

Thanks.

 

[Somendra Misra]

Glad you solve it & thanks for the feedback. Welcome Back anytime.

Regards,