# An array in a cell used by formula in another cell

#### mrblister

##### Board Regular
CELL ENTRY
A1 array formula that outputs {5,6,7}
A2 {5,6,7}
A3 ={5,6,7}

I have a formula in a cell, and I want to use A1 in that formula. I can't figure out how to do it.

For example, let's say the formula is ={sum({5,6,7})}. That gives 18. The formula ={sum(indirect(A2))} also gives 18. But I want to use the output in cell A1 in my formula. I can't get A1 or A3 to output 18 in the array sum formula.

Does anybody have any suggestions on how to do this?

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### T. Valko

##### Well-known Member
Can you have the array returned as a simple comma delimited string like 5,6,7?

#### mrblister

##### Board Regular
Can you have the array returned as a simple comma delimited string like 5,6,7?
I don't think so. If that is possible, it's beyond my abilities. The formula in A1 is an array formula that returns an array. Specifically, it's ={MATCH((B1:F1),RowOfHeaders,0)}. It returns an array (like {0,2,34,1,0}).
I had thought that maybe, if I could somehow convert the answer array (in A1) into a text string, then I could refer to A1 with Indirect with my formula in another cell.

Or maybe I'm going about this the wrong way? I have a very large workbook with long formulas that I am optimizing. Many of the formulas have the section "MATCH((B5:F5),RowOfHeaders,0)" repeating, and the answer array doesn't change when it's copied down (is always calculated to be {0,2,34,1,0} with the given input data). I thought that if I could move that section out to a single cell, and just refer to that cell in other formulas, I could significantly speed up my formulas.

For a simplified example (let's say it's in A5) is something like:

That formula is copied down.

So if I could replace my formula with something like:
={sum(INDEX(Table1,indirect(\$A\$1),1)+vlookup("Key2",Table2,indirect(\$A\$1))*(indirect(\$A\$1)*12))}

My workbook should run much faster. In my workbook, the actual formula is much longer and copied down for many rows. But it's slowing down.

So I either would like to:
a) Refer to an array in a cell (generated by an array formula) used by an array formula in another cell, OR
b) Figure out some other way to make my array formulas more efficient by reducing repeating sections that output the same array (with given input data).

#### T. Valko

##### Well-known Member
About the only thing I can think of is if you were to post a SMALL sample file so we can see what you're trying to do.

Personally, I won't download files larger than 20kb. People tend to post very large files full of irrelevant data and that just makes it harder for us (the helpers) to find what we're looking for.

Replies
3
Views
385
Replies
3
Views
1K
Replies
4
Views
865
Replies
3
Views
638
Replies
4
Views
2K

1,190,857
Messages
5,983,252
Members
439,833
Latest member
CDaviess

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

### Which adblocker are you using?

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

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