Index Match with Multiple Criteria in VBA

[cgastelum]

Sub F851_Service_IndexMatch()

'Range("I2:I1073").FormulaArray = "=INDEX('[05-06-20  Service Crosswalk.xlsx]Xwalk'!$F:$F,MATCH(1,(A2='[05-06-20  Service Crosswalk.xlsx]Xwalk'!$C:$C)*(B2='[05-06-20  Service Crosswalk.xlsx]Xwalk'!$B:$B)*(F2='[05-06-20  Service Crosswalk.xlsx]Xwalk'!$D:$D)*(G2='[05-06-20  Service Crosswalk.xlsx]Xwalk'!$A:$A),0))"

Above is the Index Match Formula I have been using to pull information from a separate worksheet using 3 different criteria. When I try to enter the array formula in VBA and run the macro afterwards I get this error "Unable to set the FormulaArray property of the Range Class", which I assuming I'm getting because the formula is greater than 255 characters. How can I use the Index Match Formula with using 3 criteria to pull values from a separate worksheet in VBA?

Any help would be appreciated. Let me know if there is anything I need to clarify.

 

[jasonb75]

Welcome to MrExcel

You're right in assuming that the length of the formula is the issue, you can get around that by shortening the formula to enter it into the sheet, then using find and replace to complete it.
I've done it here by removing the file name from the formula to enter the array, then replacing the sheet name with the file and sheet name to complete the formula.
The additional lines of code are to prevent the popup asking you to select the correct workbook if there is no Xwalk sheet in the workbook with the code.

Sub F851_Service_IndexMatch()
Application.DisplayAlerts = False
ThisWorkbook.UpdateLinks = xlUpdateLinksNever

    Range("I2:I1073").FormulaArray = "=INDEX(Xwalk!$F:$F,MATCH(1,(A2=Xwalk!$C:$C)*(B2=Xwalk!$B:$B)*(F2=Xwalk!$D:$D)*(G2=Xwalk!$A:$A),0))"
    Range("I2:I1073").Replace "Xwalk", "'[05-06-20  Service Crosswalk.xlsx]Xwalk'", xlPart, , False

ThisWorkbook.UpdateLinks = xlUpdateLinksAlways
Application.DisplayAlerts = True
End Sub

 

[cgastelum]

Welcome to MrExcel

You're right in assuming that the length of the formula is the issue, you can get around that by shortening the formula to enter it into the sheet, then using find and replace to complete it.
I've done it here by removing the file name from the formula to enter the array, then replacing the sheet name with the file and sheet name to complete the formula.
The additional lines of code are to prevent the popup asking you to select the correct workbook if there is no Xwalk sheet in the workbook with the code.

Sub F851_Service_IndexMatch()
Application.DisplayAlerts = False
ThisWorkbook.UpdateLinks = xlUpdateLinksNever

    Range("I2:I1073").FormulaArray = "=INDEX(Xwalk!$F:$F,MATCH(1,(A2=Xwalk!$C:$C)*(B2=Xwalk!$B:$B)*(F2=Xwalk!$D:$D)*(G2=Xwalk!$A:$A),0))"
    Range("I2:I1073").Replace "Xwalk", "'[05-06-20  Service Crosswalk.xlsx]Xwalk'", xlPart, , False

ThisWorkbook.UpdateLinks = xlUpdateLinksAlways
Application.DisplayAlerts = True
End Sub

It worked. Thank you so much.
I have one more question. When the formula is carried down column I, each cell still references row 2 and doesn't reference their respective row. Is there a way to change that?

 

[jasonb75]

Taking a different approach, see if this works in place of the FormulaArray line.

Personally, I wouldn't use full columns with such a formula, filling it to over 1000 cells is probably a very slow process.

Range("I2:I1073").FormulaR1C1 = "=INDEX(Xwalk!C6:C6,AGGREGATE(15,6,ROW(XWalk!C6:C6)/(RC1=Xwalk!C3:C3)/(RC2=Xwalk!C2:C2)/(RC6=Xwalk!C4:C4)/(RC7=Xwalk!C1:C1),1))"