Forum Discussion
David
Oct 19, 2017Copper Contributor
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 | COL C Catalog # | COL D Order Number | COL E On rebate report | COL F Price Line | Product ID | Description | Shipping Branch | Ship Qty |
1110 | ABC CO | 10250T1 | S1210754.001 | ABC CO | XXX | 8286 | XYZ | 1 | 50 |
1661 | DEF CO | 10250T1 | S1210215.001 | GHI | XXX | 8286 | XYZ | 1 | 2 |
The data looks like this:
Rebate Report
COL A Customer Name............. | COL B Catalog Number.......................... | COL C Invoice#....... | Contract No.......... | ShipDate. | UPC | Qty Shpd.. |  Actual Cost.. |  Rebate Cost |  Dif Amt...... |  Rebate Ext... |
DEF CO | 10250Q1643N | S1206376.013 | 003473 | 1/18/2017 | 7821134567
|
No RepliesBe the first to reply