# Too Many Array Formulas!

#### Gene Smolko

##### New Member
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)))),"")

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Hi, you could try something similar to this set-up.

Excel 2012
ABCD
17< Countifs in its own cell
2
3
41< Helper column11< Formula to return the results
5290
6470
7695
8779
9879
10926
11
Sheet1
Cell Formulas
RangeFormula
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.

Last edited:

Replies
4
Views
713
Replies
6
Views
504
Replies
0
Views
194
Replies
3
Views
332
Replies
3
Views
235

1,196,253
Messages
6,014,267
Members
441,809
Latest member
pawansher2002

### 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?

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