Excel 2013 Vlookup with multiple date ranges

[ian machin]

Hi All,
I am trying to use the Vlookup function to search for product costs from a list to match a sales order date. My downloaded sales data show the sales order delivery date , column "C" and I am trying to match the production costs with the correct date range , columns "N" and "O".
The cells in yellow range G5:H7 show would the expected result should be. Struggling to set the "only use the vlookup if the date range is correct"
Many thanks in advance.

Excel 2012

A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
1
Sales Table
Lookup Table
2
3
4
Product 
Product Description
Date
Qty
Unit Price
Gross Sales
Prod Cost 1
Prod Cost 2
Product
Prod Cost 1
Prod Cost 2
Date From
Date To
5
935
Gift Bag
10/07/2015
2
£10.00
£20.00
4.00
2.00
935
£4.00
£2.00
05/07/2015
15/07/2015
6
935
Gift Bag
20/07/2015
3
£10.00
£30.00
3.00
3.50
935
£3.00
£3.50
19/07/2015
31/07/2015
7
936
Gift Set
12/07/2015
4
£15.00
£60.00
4.00
1.00
936
£4.00
£1.00
05/07/2015
31/12/2015
8
937
£5.00
£1.50
05/07/2015
31/12/2015
9
938
£3.50
£2.00
05/07/2015
31/12/2015
10
939
£2.00
£4.00
05/07/2015
31/12/2015
11
940
£1.00
£1.50
05/07/2015
31/12/2015

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>

</thead><tbody>

</tbody>
Sheet1​

Ian Machin

 

[jorismoerings]

Hi Ian,

thought i had it but it fell through.

 

[jorismoerings]

Hi Ian,

This should be it.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Sales Table										Lookup Table
2
3
4	Product	Product Description	Date	Qty	Unit Price	Gross Sales	Prod Cost 1	Prod Cost 2			Product	Prod Cost 1	Prod Cost 2	Date From	Date To
5	935	Gift Bag	10-7-2015	2	£10.00	£20.00	4,00	2,00	£4.00	£2.00	935	£4.00	£2.00	5-7-2015	15-7-2015
6	935	Gift Bag	20-7-2015	3	£10.00	£30.00	3,00	3,50	£3.00	£3.50	935	£3.00	£3.50	19-7-2015	31-7-2015
7	936	Gift Set	12-7-2015	4	£15.00	£60.00	4,00	1,00	£4.00	£1.00	936	£4.00	£1.00	5-7-2015	31-12-2015
8											937	£5.00	£1.50	5-7-2015	31-12-2015
9											938	£3.50	£2.00	5-7-2015	31-12-2015
10											939	£2.00	£4.00	5-7-2015	31-12-2015
11											940	£1.00	£1.50	5-7-2015	31-12-2015
Sheet2

Cell Formulas
Range	Formula
I5	=INDEX($L$5:$L$11,SUMPRODUCT(($K$5:$K$11=A5)*($N$5:$N$11<=C5)*($O$5:$O$11>=C5)*ROW($L$5:$L$11))*0)
J5	=INDEX($M$5:$M$11,SUMPRODUCT(($K$5:$K$11=A5)*($N$5:$N$11<=C5)*($O$5:$O$11>=C5)*ROW($M$5:$M$11))*0)

 

[ian machin]

Thank you so much , I know we all like the excel challenge but this is greatly appreciated. Never considered that approach
Ian M

 

[Aladin Akyurek]

G5, control+shift+enter, not just enter, copy across, and down:

=INDEX($L$5:$M$11,MATCH(1,IF($K$5:$K$11=$A5,IF($C5>=$N$5:$N$11,
    IF($O$5:$O$11>=$C5,1))),0),MATCH(G$4,$L$4:$M$4,0))

 

[ian machin]

Thank you for your answer, works perfectly, very much appreciated

Ian M

 

[Aladin Akyurek]

Thank you for your answer, works perfectly, very much appreciated

Ian M

You are welcome.