INDEX MATCH with IF

[russelluno123]

Hi

i was wondering if i could use index match with IF to find the production volume by matching the delivery date and post it to the relavant month in sales

[TABLE="width: 1080"]
<colgroup><col><col><col><col><col><col><col><col><col><col span="5"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Production Volume[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Sales [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Invoice[/TD]
[TD]Jan-18[/TD]
[TD]Feb-18[/TD]
[TD]Mar-18[/TD]
[TD]Apr-18[/TD]
[TD]Shipping [/TD]
[TD]Consignment[/TD]
[TD]Date Of Delivery [/TD]
[TD] Invoice [/TD]
[TD][/TD]
[TD]Jan-18[/TD]
[TD]Feb-18[/TD]
[TD]Mar-18[/TD]
[TD]Apr-18[/TD]
[/TR]
[TR]
[TD]INV1525[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]300[/TD]
[TD]150[/TD]
[TD]60[/TD]
[TD]30[/TD]
[TD]01-Apr[/TD]
[TD]INV1525[/TD]
[TD][/TD]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]INV1526[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]300[/TD]
[TD]45[/TD]
[TD]45[/TD]
[TD]01-Apr[/TD]
[TD]INV1526[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]INV1527[/TD]
[TD]200[/TD]
[TD]300[/TD]
[TD]100[/TD]
[TD]300[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]02-Mar[/TD]
[TD]INV1527[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]INV1528[/TD]
[TD]400[/TD]
[TD]400[/TD]
[TD]130[/TD]
[TD]300[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]01-Jan[/TD]
[TD]INV1528[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Is this what you need

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Invoice	Jan-18	Feb-18	Mar-18	Apr-18	Shipping	Consignment	Date Of Delivery		Invoice	Jan-18	Feb-18	Mar-18	Apr-18
2	INV1525	100	100	300	150	60	30	01-Apr		INV1525	100	100	300	150
3	INV1526	200	200	200	300	45	45	01-Apr		INV1526	200	200	200	300
4	INV1527	200	300	100	300	30	30	02-Mar		INV1527	200	300	100	300
5	INV1528	400	400	130	300	0	0	01-Jan		INV1528	400	400	130	300
Data

Cell Formulas
Range	Formula
K2	=INDEX($B$2:$E$5,MATCH($J2,$A$2:$A$5,0),MATCH(K$1,$B$1:$E$1,0))

Fill formula down & across

 

[russelluno123]

Hi Fluff

thank you for replying so promptly. I want to get the invoice to be in the sales depending on the delivery date (column H ) not the production date ie in sales should match the same invoice number from production but populate the corresponding month in sales based on delivery date (column H). Thanks very much for your input

 

[Fluff]

Can you please show some examples of the expected outcome

 

[russelluno123]

[TABLE="width: 1016"]
<colgroup><col><col><col><col><col><col><col><col><col span="5"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Production Volume[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Sales [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LON. No[/TD]
[TD]Jan-18[/TD]
[TD]Feb-18[/TD]
[TD]Mar-18[/TD]
[TD]Apr-18[/TD]
[TD]Shipping [/TD]
[TD]Consignment[/TD]
[TD]Date Of Delivery [/TD]
[TD] LON No. [/TD]
[TD]Jan-18[/TD]
[TD]Feb-18[/TD]
[TD]Mar-18[/TD]
[TD]Apr-18[/TD]
[/TR]
[TR]
[TD]INV1525[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]60[/TD]
[TD]30[/TD]
[TD]Apr-18[/TD]
[TD]INV1525[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]INV1526[/TD]
[TD][/TD]
[TD]200[/TD]
[TD][/TD]
[TD][/TD]
[TD]45[/TD]
[TD]10[/TD]
[TD]Mar-18[/TD]
[TD]INV1526[/TD]
[TD][/TD]
[TD][/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]INV1527[/TD]
[TD][/TD]
[TD]300[/TD]
[TD][/TD]
[TD][/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]Apr-18[/TD]
[TD]INV1527[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]INV1528[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]400[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]Apr-18[/TD]
[TD]INV1528[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]400[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Unfortunately this is now beyond my somewhat limited knowledge of formulae.
Hopefully someone else will step in

 

[russelluno123]

no worries thanks for trying

 

[Fluff]

Figured something out

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Invoice	Jan-18	Feb-18	Mar-18	Apr-18	Shipping	Consignment	Date Of Delivery		Invoice	01/01/2018	01/02/2018	01/03/2018	01/04/2018
2	INV1525	100				60	30	01/04/2018		INV1526				200
3	INV1526		200			45	45	01/02/2018		INV1525		100
4	INV1527		300			30	30	01/03/2018		INV1527			300
5	INV1528				400	0	0	01/01/2018		INV1528	400
Jan

Cell Formulas
Range	Formula
K2	=IF($H2=K$1,AGGREGATE(4,6,INDEX($B$2:$E$5,MATCH($J2,$A$2:$A$5,0),0)),"")
L2	=IF($H2=L$1,AGGREGATE(4,6,INDEX($B$2:$E$5,MATCH($J2,$A$2:$A$5,0),0)),"")
M2	=IF($H2=M$1,AGGREGATE(4,6,INDEX($B$2:$E$5,MATCH($J2,$A$2:$A$5,0),0)),"")
N2	=IF($H2=N$1,AGGREGATE(4,6,INDEX($B$2:$E$5,MATCH($J2,$A$2:$A$5,0),0)),"")