How to spill a second column from the spill formula in one cell? (two-dimensional spill :) ?)

[Rnkhch]

Hello,

I have a filter function in D1 that spills the contents of column A that match the filter criteria. I was wondering if it is possible to have the formula in the same cell spill the content of the adjacent column (B) in column E? In the attached example, I have manually filled in the contents of column B in E right next to each cell of the spill, but I was hoping that can be done in one step automatically

(I was thinking to use INDEX/MATCH to grab the column B content separately, but it won't work where there are multiple numbers in column A that are the same.)

Thanks for any input!

Book1
	A	B	C	D	E	F
1	12	u		37	f
2	37	f		32	h
3	32	h		49	z
4	16	o		43	g
5	49	z		48	h
6	8	f		24	r
7	43	g		23	t
8	48	h		46	j
9	18	f		50	l
10	24	r
11	23	t
12	17	y
13	46	j
14	19	k
15	50	l
16
Sheet1

Cell Formulas
Range	Formula
D1:D9	D1	=LET(x,A1:A15,FILTER(x,x>20))
Dynamic array formulas.

 

[AhoyNC]

Maybe just use the FILTER function.

Book1
	A	B	C	D	E
1	12	u		37	f
2	37	f		32	h
3	32	h		49	z
4	16	o		43	g
5	49	z		48	h
6	8	f		24	r
7	43	g		23	t
8	48	h		46	j
9	18	f		50	l
10	24	r
11	23	t
12	17	y
13	46	j
14	19	k
15	50	l
Sheet1

Cell Formulas
Range	Formula
D1:E9	D1	=FILTER(A1:B15,A1:A15>20,"No Data")
Dynamic array formulas.

 

[Rnkhch]

Awesome! Thank you. I was overthinking

One more thing. If a cell in column B is blank, then the spill shows a zero instead of blank for that spot. How do I avoid that?

 

[Rick Rothstein]

One more thing. If a cell in column B is blank, then the spill shows a zero instead of blank for that spot. How do I avoid that?

Maybe this way...

=FILTER(""&A1:B15,A1:A15>20,"No Data")

 

[Peter_SSs]

If you want to maintain the column A values as numbers not text in the result, perhaps

=LET(rw,A1:A15>20,CHOOSE({1,2},FILTER(A1:A15,rw),FILTER(B1:B15&"",rw)))

 

[Peter_SSs]

Another way to maintain the numbers as such

22 07 20.xlsm
	A	B	C	D	E
1	12	u		37	f
2	37	f		32	h
3	32	h		49	z
4	16	o		43
5	49	z		48	h
6	8	f		24	r
7	43			23
8	48	h		46	j
9	18	f		50	l
10	24	r
11	23
12	17	y
13	46	j
14	19	k
15	50	l
Filter

Cell Formulas
Range	Formula
D1:E9	D1	=LET(f,FILTER(""&A1:B15,A1:A15>20),IFERROR(f+0,f))
Dynamic array formulas.

 

[Rick Rothstein]

Argh, I never noticed that my formula in Message #4 outputted Column A values as text.

If you don't mind having your results sorted by the Column A values, then this formula should also work to preserve Column A values as numbers...

=LET(x,A1:B15,SORT(IF((A:A x)>20,IF(x="","",x),"")))

 

[Peter_SSs]

If you don't mind having your results sorted by the Column A values, then this formula should also work ..

.. providing also that the spill range is not going to be used elsewhere as that produces a spill range as big as the original range rather than just the minimum required number of rows.

 

[Rick Rothstein]

My take on this: Since one cannot not know in advance how many rows will meet the stated criteria, I think the spill range should always be as large as the original range (for a calculation such as this one) so that no one can put data in any part of it without raising a Spill error to alert them. That way, if the range gets bigger due to a change in the original data, the other data you referred to won't have to be moved out of the way to make way for the increased spill's output range.

 

[Peter_SSs]

Yes, but if that formula was in say N1 then another formula might use N1# in it somewhere and that would drag in all 15 rows, including any blanks at the bottom which could result in errors or no error but incorrect/misleading results in that other formula.