Index Match with multiple criteria conversion to VBA using an array formula

Question

I have a table from which I am trying to retrieve data based on multiple criteria. I am using Index Match for this. If I manually paste the formula in excel it works in both the below cases.

First I entered the Array formula using the full column range from the raw table in the Index Match formula

    Selection.FormulaArray = _
    "=IF(ISNA((INDEX(Data!C2:C8,MATCH(1,(Data!C2=RC2)*(Data!C3=RC3)*(Data!C4=R3C)*(Data!C5=R4C),0),4))),""F"",(INDEX(Data!C2:C8,MATCH(1,(Data!C2=RC2)*(Data!C3=RC3)*(Data!C4=R3C)*(Data!C5=R4C),0),4)=R4C))"

This works but very runs very slow for large data. Hence I have modified the formula for a specific range from the table e.g. 30000 rows. This works when I enter it manually in excel with row and column references, this also reduces the processing time considerably. But throws an error when I try to use it through the code in VBA using the R1C1 style. Below is the sample of the code with range for 40000 rows

    Selection.FormulaArray = _
    "=IF(ISNA((INDEX(Data!R2C2:R40000C8,MATCH(1,(Data!R2C2:R40000C2=R[-1]C2)*(Data!R2C3:R40000C3=R[-1]C3)*(Data!R2C4:R40000C4=R3C)*(Data!R2C5:R40000C5=R4C),0),4))),""F"",(INDEX(Data!R2C2:R40000C8,MATCH(1,(Data!R2C2:R40000C2=R[-1]C2)*(Data!R2C3:R40000C3=R[-1]C3)*(Data!R2C4:R40000C4=R3C)*(Data!R2C5:R40000C5=R4C),0),4)=R4C))"

the error shown is

"Run time error 1004. Unable to set the Formula Array property of the range class."

I feel the error is due to the style of syntax on how it should be entered. Thanks in advance for the support

Vityata Vityata · Accepted Answer · 2018-06-12T12:58:51

The easiest way is the following:

Write the formula in Excel;
Go to the Developer tab and click "Record a macro";
Before doing point 2, click on this small square:

Then press a few times the cell with the formula, it will be "translated";

Index Match with multiple criteria conversion to VBA using an array formula

1 Answers