Too Many Array Formulas!

[Gene Smolko]

Hello All,

I am using an array formula to extract a list of data based on two criteria. One of the criteria is a cell reference that is a drop down box that contains a list of codes. When a new code is selected, this changes the first criteria and the array formula results update with the new results based on the new code.

The next wrinkle is that in addition to this one column of returned data, I also need the data in adjacent rows so I what I ended up with is a number of columns with array formulas to return data from these adjacent rows. Since there is a large number of possible returns, all these columns with array formulas in them have to be copied down about 500 rows. As you can imagine, when I change the code in the dropdown, it takes quite a while to calculate the new results. It works, but it takes a while.

What I was wondering is if there might be a formula I could use to replace all the other array formulas in the other columns, essentially have them reference off the row of the return in the first array formula column. The formula would take that row reference and return the value in the column specified by the formula. Any thoughts?
Here's the array formula I'm using:

IFERROR(IF(COUNTIFS('CMA_history Cmbd'!$X$1:$X$9874,'MIP analysis data'!$A$4,'CMA_history Cmbd'!$W$1:$W$9874,"V"),INDEX('CMA_history Cmbd'!E$1:E$9874,SMALL(IF('CMA_history Cmbd'!$X$1:$X$9874='MIP analysis data'!$A$4,IF('CMA_history Cmbd'!$W$1:$W$9874="V",ROW('CMA_history Cmbd'!$X$1:$X$9874)-ROW('CMA_history Cmbd'!$X$1)+1)),ROW(Y1)))),"")

 

[FormR]

Hi, you could try something similar to this set-up.

Excel 2012
	A	B	C	D
1	7	< Countifs in its own cell
2
3
4	1	< Helper column	11	< Formula to return the results
5	2		90
6	4		70
7	6		95
8	7		79
9	8		79
10	9		26
11
Sheet1

Cell Formulas
Range	Formula
A1	=COUNTIFS('CMA_history Cmbd'!$X$1:$X$9874,'MIP analysis data'!$A$4,'CMA_history Cmbd'!$W$1:$W$9874,"V")
A4	{=IF(ROWS($A$4:A4)>$A$1,"",SMALL(IF('CMA_history Cmbd'!$X$1:$X$9874='MIP analysis data'!$A$4,IF('CMA_history Cmbd'!$W$1:$W$9874="V",ROW('CMA_history Cmbd'!$X$1:$X$9874)-ROW('CMA_history Cmbd'!$X$1)+1)),ROWS($A$4:A4)))}
C4	=IF($A4="","",INDEX('CMA_history Cmbd'!E$1:E$9874,$A4))
Press CTRL+SHIFT+ENTER to enter array formulas.