How to Get an Array of Values to Display When Evaluating a Formula

legalhustler

Well-known Member
The following array formula returns only a single value to a cell and when I evaluate it (pressing F9 key) it only shows for example "101000", this is because it's only looking at a single cell (i.e. see blue A2)

However, when I change the formula from A2 to an array of cells A2:A12 (see red A2:A12) it return a #N/A error.

How can I fix my formula to show an array of answers instead of a single value?

Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Anyone??

However, when I change the formula from A2 to an array of cells A2:A12 (see red A2:A12) it return a #N/A error.

How can I fix my formula to show an array of answers instead of a single value?
I am not sure there is a fix for what you are doing here... arrays in array formulas need to be the same size whereas you have differing sizes to your arrays...

\$A\$2:\$A\$12 <==> Rules!\$D\$3:\$D\$6 <==> Rules!\$D\$3:\$E\$6

The way array formulas work, the first element of all the arrays are used in the formula, then second element of all the arrays are used in the formula, and so on until all the elements have been processed... this creates an array of answers which is then processed by whatever function you feed them to. Basically, we are talking about a single loop through the arrays.... your formula looks like you are trying to evaluate some kind of double loop... if I am right about that, I think you may need to turn to a VB macro solution instead.

Perhaps this might help: alter the VLOOKUP statement to Exact Match: VLOOKUP(MID(A2,Rules!\$I\$3,Rules!\$G\$3),Rules!\$D\$3:\$E\$6,2,0)

I tried to reconstruct all this. I discovered that Rothstein is correct: the array sizes don't match. See here, the green cells work when the arrays are sized the same but the pink cells fail when the lookup table is a different size.

Excel 2012
ABCDEFG
1start1
2num_char3
3
4test datavlookup table
5constantconcon12
6variablevarvar23
7combinedcomcom34
8right45
912312wrong56
10323ok67
11334
12
13#N/A#N/A12
14#N/A23
15#N/A34
Sheet35
Cell Formulas
RangeFormula
B5=MID(A5,1,3)
B6=MID(A6,1,3)
B7=MID(A7,1,3)
C9{=SUM(--(MID(\$A\$5:\$A\$7,\$B\$1,\$B\$2)=\$F\$5:\$F\$7))}
C10{=SUM(--(MID(\$A\$5:\$A\$7,\$B\$1,\$B\$2)=\$F\$5:\$F\$7))}
C11{=SUM(--(MID(\$A\$5:\$A\$7,\$B\$1,\$B\$2)=\$F\$5:\$F\$7))}
C13{=SUM(--(MID(\$A\$5:\$A\$7,\$B\$1,\$B\$2)=\$F\$5:\$F\$10))}
C14{=SUM(--(MID(\$A\$5:\$A\$7,\$B\$1,\$B\$2)=\$F\$5:\$F\$10))}
C15{=SUM(--(MID(\$A\$5:\$A\$7,\$B\$1,\$B\$2)=\$F\$5:\$F\$10))}
D9:D11{=VLOOKUP(MID(\$A\$5:\$A\$7,1,3),\$F\$5:\$G\$7,2,0)}
D13:D15{=VLOOKUP(MID(\$A\$5:\$A\$7,1,3),\$F\$5:\$G\$10,2,0)}
Press CTRL+SHIFT+ENTER to enter array formulas.

I am not sure there is a fix for what you are doing here... arrays in array formulas need to be the same size whereas you have differing sizes to your arrays...

\$A\$2:\$A\$12 <==> Rules!\$D\$3:\$D\$6 <==> Rules!\$D\$3:\$E\$6

The way array formulas work, the first element of all the arrays are used in the formula, then second element of all the arrays are used in the formula, and so on until all the elements have been processed... this creates an array of answers which is then processed by whatever function you feed them to. Basically, we are talking about a single loop through the arrays.... your formula looks like you are trying to evaluate some kind of double loop... if I am right about that, I think you may need to turn to a VB macro solution instead.

My first formula gives me the correct answer, but when I evaluate it just shows one value because I am only looking up cell A2. If I lookup multiple items, A2:A12, it won't return an array of values. I've read somewhere before that some Excel functions can handle different array sizes.

My formula is looking up a value from a text file determining if it's in the "Rules" sheet in cells D3:D6, and returning the corresponding value from E3:E6. But as I have said, I want to lookup all the values from the text file, determine if it's in the "Rules" sheet in cells D3:D6 then return all the corresponding values from cells E3:E6. I would like to avoid using macros and any helper columns.

Last edited:
Firstly, the other replies about matching the dimensions of the arrays are correct, and you need to look at that.
When you align the arrays you will face another issue: How do you expect to display an array of results in a formula in a single cell.

Firstly, the other replies about matching the dimensions of the arrays are correct, and you need to look at that.
When you align the arrays you will face another issue: How do you expect to display an array of results in a formula in a single cell.

I am not expecting my array of results to show in a single cell. The intent of the formula was to use it inside the data array argument of the FREQUENCY function so that I could come up with a unique count.

Replies
8
Views
127
Replies
7
Views
152
Replies
4
Views
176
Replies
1
Views
411
Replies
1
Views
198

1,206,944
Messages
6,075,778
Members
446,154
Latest member
Dirk46

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.

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