Working with an array formula

[Kinghart]

Hello Everyone… Can Anyone Help Me With This Situation… What I'm Try To Do Is:

In Column B When I marked with an "X", Column Should Show What Is In Column A... A Bit Comfusing But Here Are The Formula I'm Using.

Say In A2 = Product1

In G2 =IF(B2<>"",ROW(),"")

In E2{=IF(ROW($A2:$A16)-ROW($B$2)+1>COUNT(G2:G16),"",INDEX($A:$A,SMALL(G2:G16,ROW(INDIRECT("1:"&ROWS(B2:B16))))))}


I've also noticed that From row 2 - 8 The formula works... But from row 9 onwards it's not working.... And also I've noticed that when I change the formula in E2, All the others to E8 changes automatically leaving the rest from E9... Any suggestions... How can I change a part of an array formula???

 

[Hui]

Kinghart


Welcome to the Chandoo.org Forums


I extended the 3 ranges $A2:$A16, B2:B16, G2:G16

all to the Bottom of my data and that fixed it for me

 

[Kinghart]

Nop... tried that too... doesn't work the way I want it to...

 

[Hui]

?

[pre]

a	x			a		2
b				d
c				e
d	x			g		5
e	x			h		6
f				i
g	x			k		8
h	x			m		9
i	x			n		10
j				p
k	x			r		12
l				u
m	x			v		14
n	x			y		15
o
p	x					17
q
r	x					19
s
t
u	x					22
v	x					23
w
x
y	x					26
z

[/pre]

 

[Kinghart]

WOW.... I'll try that again... thank you Hui

 

[Kinghart]

it still don't work... I don't know what's wrong with that.... did u really got it... where am i wrong at???

 

[Luke M]

Perhaps this will do it?

=IF(ROWS(A$2:A2)>COUNT(G:G),"",INDEX(A:A,SMALL(G:G,ROW(A1))))


Note that this is not an array formula.


My guess is that you've got 2 types of array formulas. One type is where you have a single cell with an array formula. The formula handles the array, and spits out a single output. The other (sounds like E2:E8) is where you've selected multiple cells and applied an array formula. This causes the range of cells to handle the array, and each cell with that array spits out part of the solution.

 

[Kinghart]

Thanks Luke M... It's not an array but works perfect...

 

[Hui]

I Selected E2:E30

Then pasted the formula in and Ctrl Shift Enter

So that the formula is the same formula in each cell