Hello!

I am attempting to create a spreadsheet that essentially copies several columns (A,B,C,Q,AG,BC,BD,BE,BF,BG,BH) from every row from another sheet that contains a specific value. The source spreadsheet is about 1500 rows deep and 63 columns wide. I need to copy every row where the value in column AG is "Spoke directly to a representitive", and none of columns BC-BH are blank. If neither of those two filter values are met then I want the line skipped completely.

I know how to do it using an array (mostly). I've worked out the below formula to return all the values I need for column C (and adding extra clauses to filter out the blanks in BC-BH). I was then going to use offsets to do it again for each of the columns I need. The issue is that just this partial formula for ONE of the 11 columns I need to generate takes about 2 minutes to process and slows the sheet down to a crawl. I can't imagine trying to implement this across the entire workbook (I have about 5 more sheets in the workbook that need similar filtering. I've done some research, and it seems that a Helper column is what I need to make the calculations more efficient, but can't for the life of me figure it out. Can anyone help?

Thanks!

Formula: copy to clipboard 
=IFERROR(offset(INDEX('Raw Data Input'!C$2:C$1500, MATCH(0, COUNTIF($C$1:C1, 'Raw Data Input'!C$2:C$1500) + IF('Raw Data Input'!C$2:C$1500="Completely by the automated phone system",1,0) +IF(OFFSET('Raw Data Input'!C$2:C$1500,0,30)="Used the automated phone system and then talked to representitive",1,0), 0))-1), "")



*the misspelling of representative is in the source, I am exporting from, so it needs to stay misspelled.