New FILTER function cannot accept an array in the filter value?

[BrianGGG]

Hi. I have the following reference table:

	A	B
Item	Quarter
a
b
c
d
e
f
g
h
i
j
k
l
m

<tbody>
[TD="align: center"]135[/TD]

[TD="align: center"]136[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]137[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]138[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]139[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]140[/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]141[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]142[/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]143[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]144[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]145[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]146[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]147[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]148[/TD]

[TD="align: right"]3[/TD]

</tbody>

Summary​

My goal is to "pivot" by the quarter column to create the following output:

	E	F	G	H

<tbody>
[TD="align: center"]136[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC7CE]#FFC7CE[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC7CE]#FFC7CE[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC7CE]#FFC7CE[/URL] , align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC7CE]#FFC7CE[/URL] , align: center"]4[/TD]

[TD="align: center"]137[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]b[/TD]
[TD="align: center"]e[/TD]

[TD="align: center"]138[/TD]
[TD="align: center"]f[/TD]
[TD="align: center"]h[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]g[/TD]

[TD="align: center"]139[/TD]
[TD="align: center"]j[/TD]
[TD="align: center"][/TD]
[TD="align: center"]i[/TD]
[TD="align: center"][/TD]

[TD="align: center"]140[/TD]
[TD="align: center"]k[/TD]
[TD="align: center"][/TD]
[TD="align: center"]m[/TD]
[TD="align: center"][/TD]

[TD="align: center"]141[/TD]
[TD="align: center"]l[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Summary​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E137[/TH]
[TD="align: left"]=FILTER(t_Items[Item],t_Items[Quarter]=E136)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F137[/TH]
[TD="align: left"]=FILTER(t_Items[Item],t_Items[Quarter]=F136)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G137[/TH]
[TD="align: left"]=FILTER(t_Items[Item],t_Items[Quarter]=G136)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H137[/TH]
[TD="align: left"]=FILTER(t_Items[Item],t_Items[Quarter]=H136)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]




As you can see above, the FILTER function works to get the right answer, but I had to copy and paste the filter function across to all four columns.
I was disappointed to see that I got a #VALUE error when I tried to spill a single formula with the following:

=FILTER(t_Items[Item],t_Items[Quarter]=E136:H136)

I can't seem to use an array in the FILTER criteria. Is this the expected behavior and there is no way around this?



thanks
BrianGGG