adding an Array Function to a table

[etaf]

If i have a table in excel A1:C20 say
Then i add an array function , like say Filter(E2:G20,E2:E20="fred")
because in a table , it tries to copy the formula into each row - it gives a #spill error and where the filter range does not complete the entire table you get #calc errors
how do you get around that

perhaps add the formula first and then - insert table later
I know if you do not put the formula in after creating a table then the formula does NOT copy
found that yesterday with a different issue - of inserting rows in a table
Tried that and it still gives a #spill error

Book1
	A	B	C	D	E	F	G
1	Column1	Column2	Column3
2	#SPILL!				fred	20	100
3	#SPILL!				fred	20	100
4	#SPILL!				fred	20	100
5	#SPILL!				fred	20	100
6	#SPILL!				jon	20	100
7	#SPILL!				jill	20	100
8	#SPILL!				fred	20	100
9	#SPILL!				sally	20	100
10	#SPILL!				fred	20	100
11	#CALC!
12	#CALC!
13	#CALC!
14	#CALC!
15	#CALC!
16	#CALC!
17	#CALC!
18	#CALC!
19	#CALC!
20	#CALC!
21	#CALC!
Sheet1

Cell Formulas
Range	Formula
A2:A21	A2	=FILTER(E2:G10,E2:E10="fred")

 

[kevin9999]

Probably not what you're looking for but check out this link Array formulas and functions in Excel - examples and guidelines.
Search for "How to work with multi-cell array formulas" and note point 6.
Wish I could be of more help, but I've tried to do what you're attempting before & hit the same barrier.

 

[etaf]

thanks that was exactly what i was after really , some sort of confirmation on "is not possible" or "how to do it"

i had looked through that webpage earlier , but its such small note i missed it

1. You cannot use multi-cell array formulas in Excel tables.

Thanks,