Applying a multi-criteria Index Match array down a range only puts results in halfway...

[RockandGrohl]

Hello,

Got a chunky formula here.

I have some data on sheet "ads" (Advert Data 2021) that I want to match up with data in sheet "a" and bring them across..

Here's me code:

a.Activate
Range("G3:G" & Lastrow).FormulaR1C1 = "=INDEX('Advert Data 2021.csv'!C18,MATCH(1,('Advert Data 2021.csv'!C14=RC1)*('Advert Data 2021.csv'!C17=RC3)*('Advert Data 2021.csv'!C13=RC6)*('Advert Data 2021.csv'!C12=" & CLng(wcdate) & "),0))"
Range("G3:G" & Lastrow).FormulaArray = Range("G3:G" & Lastrow).Formula
Range("H3:H" & Lastrow).FormulaR1C1 = "=IF(RC7="""","""",SUMIFS(ATM!C4,ATM!C1,AdSelect!RC7))"
Range("I3:I" & Lastrow).FormulaR1C1 = "=COUNTIFS('Advert Data 2021.csv'!C14,RC1,'Advert Data 2021.csv'!C12," & CLng(wcdate) & ",'Advert Data 2021.csv'!C13,RC6,'Advert Data 2021.csv'!C17,RC3,'Advert Data 2021.csv'!C18,RC7)"
Range("G3:I" & Lastrow).Value = Range("G3:I" & Lastrow).Value

So it's concerning column G in "a"

My Lastrow is set to 591 - the last row indeed, however when I convert the Formula to a Formula Array (as you cannot enter an Array in R1C1 format) it applies the formula correctly, but only down to row 297, and the rest are #N/A

If I copy-paste the formula from the rows, above, it seems to enter correctly.

Does anyone have any ideas? I might put the formula in one cell and then fill-down, perhaps.

 

[RockandGrohl]

Range("G3").FormulaArray = "=INDEX('Advert Data 2021.csv'!$R:$R,MATCH(1,('Advert Data 2021.csv'!$N:$N=$A3)*('Advert Data 2021.csv'!$Q:$Q=$C3)*('Advert Data 2021.csv'!$M:$M=$F3)*('Advert Data 2021.csv'!$L:$L=" & CLng(wcdate) & "),0))"
Range("G3").Select
Selection.AutoFill Destination:=Range("G3:G" & Lastrow)

This appears to have worked, but the formula is still slow to calculate.

As you can see, I'm trying to match several criteria and return a result from a column in "ads" that matches all criteria. If there's a better and faster way of doing this, please let me know. Thanks.