Index Match And? Function

BHarrison

New Member
Joined
May 22, 2017
Messages
24
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


DateSales RepCustomer #Customer NameTotal Order
Jan-18
Mike1001Bellas$500
Jan-18Mike1002Barrys$30
Jan-18Brian1003Autosource$50
Feb-18Mike1001Bellas$35
Feb-18Mike1002Barrys$800
Mar-18Mike1001Bellas$200
Mar-18Brian1003Autosource$500
May-18Mike1001Bellas
$29

<tbody>
</tbody>


Looking to Populate (in Red)

Customer #Customer NameSales Rep1/1/20182/1/20183/1/20184/1/2018
1001BellasMike50035200
29
1002BarrysMike308000
0
1003AutosourceBrian500
500
0

<tbody>
</tbody>


Thank you in advance
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Try SUMPRODUCT.
Copy formula down ans across as needed.
Excel Workbook
ABCDEFG
1DateSales RepCustomer #Customer NameTotal Order
2Jan-18Mike1001Bellas$500
3Jan-18Mike1002Barrys$30
4Jan-18Brian1003Autosource$50
5Feb-18Mike1001Bellas$35
6Feb-18Mike1002Barrys$800
7Mar-18Mike1001Bellas$200
8Mar-18Brian1003Autosource$500
9Apr-18Mike1001Bellas$29
10
11
12Customer #Customer NameSales Rep1/1/20182/1/20183/1/20184/1/2018
131001BellasMike5003520029
141002BarrysMike3080000
151003AutosourceBrian5005000
Sheet
 
Upvote 0
You're welcome. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top