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

	Production Volume									Sales
Invoice	Jan-18	Feb-18	Mar-18	Apr-18	Shipping	Consignment	Date Of Delivery	Invoice		Jan-18	Feb-18	Mar-18	Apr-18
INV1525	100	100	300	150	60	30	01-Apr	INV1525		FALSE
INV1526	200	200	200	300	45	45	01-Apr	INV1526
INV1527	200	300	100	300	30	30	02-Mar	INV1527
INV1528	400	400	130	300	0	0	01-Jan	INV1528

<colgroup><col><col><col><col><col><col><col><col><col><col span="5"></colgroup><tbody>
</tbody>

 

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

Production Volume

Sales

LON. No

Jan-18

Feb-18

Mar-18

Apr-18

Shipping

Consignment

Date Of Delivery

LON No.

Jan-18

Feb-18

Mar-18

Apr-18

INV1525

100

60

30

Apr-18

INV1525

100

INV1526

200

45

10

Mar-18

INV1526

200

INV1527

300

30

30

Apr-18

INV1527

300

INV1528

400

0

0

Apr-18

INV1528

400

<colgroup><col><col><col><col><col><col><col><col><col span="5"></colgroup><tbody>
</tbody>

 

[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)),"")