Forum Discussion

David's avatar
David
Copper Contributor
Oct 19, 2017

Help on Array Formula which includes Index and Match

I have a product sales spreadsheet which is generated monthly. I also have a rebate report which is also generated monthly. I inserted an array formula into a new column (column E) in the sales spreadsheet which checks each row of data find a match of the customer name, catalog # and order number. If it find a match, it will insert the customer name into the row in Column E.

 

The formula in COL E in the products sales report is is as follows: INDEX('Rebate Report'!$A$4:$L$396,MATCH(1,('Rebate Report'!A:A=B2)*('Rebate Report'!B:B=C2)*('Rebate Report'!C:C=D2),0),1)

 

It does work and only puts a name into column E if there is a match. However for some reason it sometimes puts the wrong name into the cell in Column E and not the customer name from column B that matches. I do not know what in the formula is causing this. Thanks.

 

Product Sales Report 

COL A

Customer ID

COL B
Customer Name

COL C
Catalog #
COL D
Order Number
COL E
On rebate report

COL F

Price Line

Product IDDescriptionShipping BranchShip Qty
1110ABC CO10250T1S1210754.001ABC COXXX8286XYZ150
1661DEF CO10250T1S1210215.001GHIXXX8286XYZ1

2

The data looks like this:

Rebate Report

COL A

Customer Name.............

COL B

Catalog Number..........................

COL C

Invoice#.......

Contract No..........ShipDate.UPCQty Shpd.. Actual Cost.. Â Rebate Cost Â Dif Amt...... Â Rebate Ext... 
DEF CO10250Q1643NS1206376.0130034731/18/2017

7821134567

 

No RepliesBe the first to reply

Resources