filter function turns error if i add column in between the array

[ExcelNewbie2020]

I have this formula =FILTER($A$2:$F$11,{1,0,0,0,1}), its working fine. However, if I insert another column in between the array it turns to error (#VALUE!)..
Is there in any way to avoid the error?.. thank you

Name	Emp.#		Date	Dept.	Amt		RESULT
Name1	1001		11-05-22	A	1000		Name	amt
Name2	1002		11-06-22	B	1000		#VALUE!​
Name3	1003		11-07-22	C	1000
Name4	1004		11-08-22	A	1000
Name5	1005		11-09-22	B	1000
Name6	1006		11-10-22	C	1000
Name7	1007		11-11-22	A	1000
Name8	1008		11-12-22	B	1000
Name9	1009		13-11-22	C	1000
Name10	1010		14-11-22	A	1000

 

[RoryA]

Do you have the CHOOSECOLS function?

 

[ExcelNewbie2020]

Do you have the CHOOSECOLS function?

yes

 

[RoryA]

Assuming you always want the first and last columns:

=CHOOSECOLS($A$2:$F$11,1,columns($A$2:$F$2))

 

[ExcelNewbie2020]

Assuming you always want the first and last columns:

=CHOOSECOLS($A$2:$F$11,1,columns($A$2:$F$2))

thanks man it workssss

 

[RoryA]

Glad we could help.

 

[Fluff]

FWIW you could also use

=CHOOSECOLS($A$2:$F$11,1,-1)