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

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
671
Office Version
  1. 2010
Platform
  1. Windows
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:

VBA 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.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
671
Office Version
  1. 2010
Platform
  1. Windows
VBA Code:
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.
 

Forum statistics

Threads
1,141,705
Messages
5,707,976
Members
421,539
Latest member
zuniBM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top