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

[legalhustler]

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)

{=IF(SUM(--(MID(A2,Rules!$I$3,Rules!$G$3)=Rules!$D$3:$D$6))>0,VLOOKUP(MID(A2,Rules!$I$3,Rules!$G$3),Rules!$D$3:$E$6,2),"SGL Not Found")}

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.

{=IF(SUM(--(MID($A$2:$A$12,Rules!$I$3,Rules!$G$3)=Rules!$D$3:$D$6))>0,VLOOKUP(MID($A$2:$A$12,Rules!$I$3,Rules!$G$3),Rules!$D$3:$E$6,2),"SGL Not Found")}

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

 

[legalhustler]

Anyone??

 

[Rick Rothstein]

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.

{=IF(SUM(--(MID($A$2:$A$12,Rules!$I$3,Rules!$G$3)=Rules!$D$3:$D$6))>0,VLOOKUP(MID($A$2:$A$12,Rules!$I$3,Rules!$G$3),Rules!$D$3:$E$6,2),"SGL Not Found")}

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.

 

[DRSteele]

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)

 

[DRSteele]

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
	A	B	C	D	E	F	G
1	start	1
2	num_char	3
3
4	test data					vlookup table
5	constant	con				con	12
6	variable	var				var	23
7	combined	com				com	34
8						right	45
9		12	3	12		wrong	56
10			3	23		ok	67
11			3	34
12
13		#N/A	#N/A	12
14			#N/A	23
15			#N/A	34
Sheet35

Cell Formulas
Range	Formula
B5	=MID(A5,1,3)
B6	=MID(A6,1,3)
B7	=MID(A7,1,3)
B9	{=IF(SUM(--(MID($A$5:$A$7,$B$1,$B$2)=$F$5:$F$7))>0,VLOOKUP(MID($A$5:$A$7,$B$1,$B$2),$F$5:$G$7,2,0),"not found")}
B13	{=IF(SUM(--(MID($A$5:$A$7,$B$1,$B$2)=$F$5:$F$10))>0,VLOOKUP(MID($A$5:$A$7,$B$1,$B$2),$F$5:$G$10,2,0),"not found")}
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.

 

[legalhustler]

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.

 

[konew1]

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.

 

[legalhustler]

I was trying to come up with a solution for a problem that I have been having for quite sometime and have not had anybody be able to revise my formula that extracts and sums my data properly. I have my sample data in Post #1 in the thread below.

http://www.mrexcel.com/forum/excel-questions/859165-formula-extract-unique-data.html

 

[legalhustler]

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.