Index Match And? Function

[BHarrison]

Hi all,

Currently having an issue converting orders to a spreadsheet. I am under the assumption that Index Match would be the best route, however due to the fact that there are numerous times that the customer number appears in the list of orders, I need to match it up with dates. This is where it becomes difficult to me (or else I would just use a simple Vlookup formula).

*I started the spreadsheet with a Pivot Table, however moving forward I will only be importing new orders, I am just going to use a formula to populate the new monthly sales (after importing any new customers manually).

Simplified versions of what I am working with is below -- Yes I am looking for the 0's to be populated (if there is no order that month). If that can't be done easily, that is fine, a blank cell is okay.

USING EXCEL FOR MAC..

Orders

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Sales Rep[/TD]
[TD]Customer #[/TD]
[TD]Customer Name[/TD]
[TD]Total Order[/TD]
[/TR]
[TR]
[TD]Jan-18
[/TD]
[TD]Mike[/TD]
[TD]1001[/TD]
[TD]Bellas[/TD]
[TD]$500[/TD]
[/TR]
[TR]
[TD]Jan-18[/TD]
[TD]Mike[/TD]
[TD]1002[/TD]
[TD]Barrys[/TD]
[TD]$30[/TD]
[/TR]
[TR]
[TD]Jan-18[/TD]
[TD]Brian[/TD]
[TD]1003[/TD]
[TD]Autosource[/TD]
[TD]$50[/TD]
[/TR]
[TR]
[TD]Feb-18[/TD]
[TD]Mike[/TD]
[TD]1001[/TD]
[TD]Bellas[/TD]
[TD]$35[/TD]
[/TR]
[TR]
[TD]Feb-18[/TD]
[TD]Mike[/TD]
[TD]1002[/TD]
[TD]Barrys[/TD]
[TD]$800[/TD]
[/TR]
[TR]
[TD]Mar-18[/TD]
[TD]Mike[/TD]
[TD]1001[/TD]
[TD]Bellas[/TD]
[TD]$200[/TD]
[/TR]
[TR]
[TD]Mar-18[/TD]
[TD]Brian[/TD]
[TD]1003[/TD]
[TD]Autosource[/TD]
[TD]$500[/TD]
[/TR]
[TR]
[TD]May-18[/TD]
[TD]Mike[/TD]
[TD]1001[/TD]
[TD]Bellas
[/TD]
[TD]$29[/TD]
[/TR]
</tbody>[/TABLE]


Looking to Populate (in Red)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer #[/TD]
[TD]Customer Name[/TD]
[TD]Sales Rep[/TD]
[TD]1/1/2018[/TD]
[TD]2/1/2018[/TD]
[TD]3/1/2018[/TD]
[TD]4/1/2018[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Bellas[/TD]
[TD]Mike[/TD]
[TD]500[/TD]
[TD]35[/TD]
[TD]200
[/TD]
[TD]29
[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Barrys[/TD]
[TD]Mike[/TD]
[TD]30[/TD]
[TD]800[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]1003[/TD]
[TD]Autosource[/TD]
[TD]Brian[/TD]
[TD]50[/TD]
[TD]0
[/TD]
[TD]500
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thank you in advance

 

[RasGhul]

Have you converted your data in a table?

https://www.youtube.com/watch?v=fJnJTff4Na8

 

[AhoyNC]

Try SUMPRODUCT.
Copy formula down ans across as needed.

Excel Workbook
	A	B	C	D	E	F	G
1	Date	Sales Rep	Customer #	Customer Name	Total Order
2	Jan-18	Mike	1001	Bellas	$500
3	Jan-18	Mike	1002	Barrys	$30
4	Jan-18	Brian	1003	Autosource	$50
5	Feb-18	Mike	1001	Bellas	$35
6	Feb-18	Mike	1002	Barrys	$800
7	Mar-18	Mike	1001	Bellas	$200
8	Mar-18	Brian	1003	Autosource	$500
9	Apr-18	Mike	1001	Bellas	$29
10
11
12	Customer #	Customer Name	Sales Rep	1/1/2018	2/1/2018	3/1/2018	4/1/2018
13	1001	Bellas	Mike	500	35	200	29
14	1002	Barrys	Mike	30	800	0	0
15	1003	Autosource	Brian	50	0	500	0
Sheet

 

[BHarrison]

Thank you this worked perfectly!

 

[AhoyNC]

You're welcome. Thanks for the feedback.