User Profile
David
Copper Contributor
Joined 9 years ago
User Widgets
Recent Discussions
Can you use AND / OR in an INDEX MATCH
Hi I have am array formula that looks like this: =INDEX('Rebate report'!A:A,MATCH(1,('Rebate report'!A:A=A2)*('Rebate report'!B:B=B2)*('Rebate report'!C:C=C2),0),1) which works. I want to add in an OR function for the name in column A. I will add this name in Column T. In other words the match is correct if column A or T match A2 and COL B=B2 and COL C=C2 also match the criteria I tried using the + to add T criteria but gave me a 0 =INDEX('Rebate report'!A:A,MATCH(1,('Rebate report'!A:A=A2)*('Rebate report'!B:B=B2)*('Rebate report'!C:C=C2)+('Rebate report'!A:A=T2),0),1) Thanks for the help!139KViews0likes63CommentsHelp 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 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 78211345671.5KViews0likes0CommentsUsing specialcells to apply a formula in a macro to only visible cells
I have an autofilter on column E which enables a user to select a specific product type to analyze. The macro should then only calculate the formula on those visible cells. Currently it seems to still attempt to calculate cells in the alphabet before the products selected. for example if the product type starts with a C, then it still attempt to calculate the formula for A and B product types when I select them to see what the macro did. The macro is below and the specialcells code is near the bottom. Sub Rebate_Exception_v2() ' ' Rebate_Exception_v2 Macro ' ' Columns("E:E").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("E1").Select With Selection.Font .Color = -16776961 .TintAndShade = 0 End With ActiveCell.FormulaR1C1 = "On Rebate Report" Range("E1").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("E2").Select ActiveCell.FormulaR1C1 = "On Rebate Report" Range("E2").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Dim lastrow As Long lastrow = Range("D65000").End(xlUp).Row Selection.SpecialCells(xlCellTypeVisible).Select Range("E2").Select Selection.FormulaArray = _ "=INDEX('Rebate report'!R4C1:R5000C12,MATCH(1,('Rebate report'!C[-4]=RC[-3])*('Rebate report'!C[-3]=RC[-2])*('Rebate report'!C[-2]=RC[-1]),0),1)" Selection.AutoFill Destination:=Range("E2:E" & lastrow) 'Selection.AutoFill Destination:=Range("E2:E"& lastrow) End Sub17KViews0likes3CommentsMacro for an array formula
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) I want to create a macro so that a user can click the command button and it will insert the row E into the product sales spreadsheet and calculate the above array formula into each row as shown below each month. The rebate report and the products sales reports have a different number of rows each month. I can make the range in the formula(i.e. currently it is 'Rebate Report'!$A$4:$L$396 in the formula) for the rebate report way bigger than will ever be needed e.g. 5000 rows if that makes things simpler than making it dynamic. Thanks David 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 -8        67.60                   30.63        36.97      (295.76) 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 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 #NA XXX 8286 XYZ 1 24.2KViews0likes2Comments- 124KViews0likes0Comments
Re: Can you use AND / OR in an INDEX MATCH
Thanks Sergei Your second statement is not what I am trying to do. I want it to give a name in Column from the Rebate Report if: if A=A2 OR t=A2 AND B = B2 AND C=C2 return a cell ref for name if A=A2 AND T=A2 AND B=B2 AND C=C2 return a cell ref for name. This should return a ref and not NA. This seemed different from what you said it would do in the formula. If A not match A2 AND T also not match A2 OR B not match B2 OR C not match C2 then return NA. Let me know if this makese sense. Thanks.127KViews0likes59CommentsMatching multiple columns in 2 spreadsheets
I have 2 monthly spreadsheets (example of column formats below): 1. A rebate report 2. Products sales spreadsheet Where the customer name, catalog # and order number in the product sales report matches the customer name, catalog # and order # in the rebate report, I want to insert the customers name in Column E. Thanks for the help. Product sales report COL A Customer ID COL B Customer Name COL C Catalog # COL D Order Number Rebate report COL A Customer Name............. COL B Catalog Number.......................... COL C Order #.......649Views0likes1CommentRe: Using specialcells to apply a formula in a macro to only visible cells
Thanks for the response Bill. After the filter is applied the data is in rows 948 to 950 and (then it hides 951 to 1208) then data displays again in row 1209 through 2113 (i.e. it is visible). I ran the macro and it did the calculation for the first 3 rows (948,949 and 950) in Column E but then it stopped. Is this because there is a break in continuous rows? What needs to change in the macro so that this does not happen and that it stops in the last row of the visible data?17KViews0likes1Comment- 4.1KViews0likes0Comments
Recent Blog Articles
No content to show