FILTER Function - Maintaining subheadings without #SPILL

[MurdochQuill]

Hi,

To begin with, I'm stuck using cell formulas due to Excel Online.... & I would ideally like this to be recalculated when formulas are recalculated.

I'm looking to format my data below like it is under "Ideal output"

I would like to avoid spill errors if I find more ID matches, and push the subheadings further down the page when more results are encountered.

The sheet looks like this,

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		INPUT DATA								Initial format				Ideal output
2		ID	Data		Terrestrial?	Desert?	Rocky?	Gas?		Terrestrial:				Terrestrial:				YES
3		Earth	3425		YES	NO	NO	NO						Earth	3425
4		Mars	456		NO	YES	NO	NO		Desert:				Earth	345
5		Jupiter	325		NO	NO	NO	YES						Desert:
6		Jupiter	245326		NO	NO	NO	YES		Rocky:				Mars	456
7		Mars	567		NO	YES	NO	NO						Mars	567
8		Mercury	3415		NO	NO	NO	NO		Gas:				Mars	43
9		Pluto	456		NO	NO	YES	NO						Rocky:
10		Pluto	4652		NO	NO	YES	NO						Pluto	456
11		Earth	345		YES	NO	NO	NO						Pluto	4652
12		Mars	43		NO	YES	NO	NO						Gas:
13														Jupiter	325
14														Jupiter	245326
Sheet1

Cell Formulas
Range	Formula
E3:E12	E3	=IF(SUM((B4:B13<>"")*(ISNUMBER(MATCH("Earth",B3,0)))), "YES", "NO")
F3:F12	F3	=IF(SUM(($B3:$B12<>"")*(ISNUMBER(MATCH("Mars",$B3,0)))), "YES", "NO")
G3:G12	G3	=IF(SUM(($B3:$B12<>"")*(ISNUMBER(MATCH("Pluto",$B3,0)))), "YES", "NO")
H3:H12	H3	=IF(SUM(($B3:$B12<>"")*(ISNUMBER(MATCH("Jupiter",$B3,0)))), "YES", "NO")
N3:O4	N3	=IFERROR(FILTER(B3:C12,ISNUMBER(MATCH(E3:E12,R2,0))),"")
N6:O8	N6	=IFERROR(FILTER(B3:C12,ISNUMBER(MATCH(F3:F12,R2,0))),"")
N10:O11	N10	=IFERROR(FILTER(B3:C12,ISNUMBER(MATCH(G3:G12,R2,0))),"")
N13:O14	N13	=IFERROR(FILTER(B3:C12,ISNUMBER(MATCH(H3:H12,R2,0))),"")
Dynamic array formulas.

But for example, if anything more is added, the following would give a SPILL error. However, I would like it to push the column further down like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1		INPUT DATA								Initial format				Ideal output
2		ID	Data		Terrestrial?	Desert?	Rocky?	Gas?		Terrestrial:				Terrestrial:				YES
3		Earth	3425		YES	NO	NO	NO						Earth	3425
4		Mars	456		NO	YES	NO	NO		Desert:				Earth	345
5		Jupiter	325		NO	NO	NO	YES						Desert:
6		Jupiter	245326		NO	NO	NO	YES		Rocky:				Mars	456
7		Mars	567		NO	YES	NO	NO						Mars	567
8		Mercury	3415		NO	NO	NO	NO		Gas:				Mars	43
9		Pluto	456		NO	NO	YES	NO						Mars	12
10		Pluto	4652		NO	NO	YES	NO						Mars	123
11		Earth	345		YES	NO	NO	NO						Mars	124
12		Mars	43		NO	YES	NO	NO						Mars	125
13														Mars	126
14														Mars	127
15														Mars	128
16														Mars	129
17														Mars	130
18														Mars	131
19														Mars	132
20														Mars	133
21														Rocky:
22														Pluto	456
23														Pluto	4652
24														Gas:
25														Jupiter	325
26														Jupiter	245326
27
28
Sheet1

Cell Formulas
Range	Formula
E3:E12	E3	=IF(SUM((B4:B13<>"")*(ISNUMBER(MATCH("Earth",B3,0)))), "YES", "NO")
F3:F12	F3	=IF(SUM(($B3:$B12<>"")*(ISNUMBER(MATCH("Mars",$B3,0)))), "YES", "NO")
G3:G12	G3	=IF(SUM(($B3:$B12<>"")*(ISNUMBER(MATCH("Pluto",$B3,0)))), "YES", "NO")
H3:H12	H3	=IF(SUM(($B3:$B12<>"")*(ISNUMBER(MATCH("Jupiter",$B3,0)))), "YES", "NO")
N3:O4	N3	=IFERROR(FILTER(B3:C12,ISNUMBER(MATCH(E3:E12,R2,0))),"")
N6:O8	N6	=IFERROR(FILTER(B3:C12,ISNUMBER(MATCH(F3:F12,R2,0))),"")
N22:O23	N22	=IFERROR(FILTER(B3:C12,ISNUMBER(MATCH(G3:G12,R2,0))),"")
N25:O26	N25	=IFERROR(FILTER(B3:C12,ISNUMBER(MATCH(H3:H12,R2,0))),"")
Dynamic array formulas.

Any help or advice on how else to solve this would be great!

 

[Fluff]

What part of that formula doesn't like Excel Online

The FilterXml function is only available on a PC.

As this i now a completely different question, you will need to start a new thread. Thanks

 

[MurdochQuill]

The FilterXml function is only available on a PC.

As this i now a completely different question, you will need to start a new thread. Thanks

Hey, no worries, thought it might be okay since I'm taking advice from a poster in the thread about formatting it differently.

But I do understand that the question needs to be specifically posted & answered in a way that is easily searchable for future reference, so I might make a new one if that's the case .