Excel FILTER function: Filter based on an array of criteria

Question

Summary: I can use the FILTER function to filter based on the specific criteria I enumerate in the formula. But if my list of criteria is long, I'd rather pull them out of the formula, list them as an array (i.e., a column of criteria), and ask Excel to filter based on that list. How do I do this?

**************

Suppose I have the follow data table on the left of the screenshot below, and the desired output on the right. Here, for each fruit, I want a horizontal listing of the sizes in which it is available.

I can achieve this as follows:

1. In cell F3, insert the formula =SORT(UNIQUE(A3:A101). This formula can be seen in the screenshot (I go to A101 in case I have a long list of fruits).
2. In cell G3, insert the following formula, which more than accounts for the different sizes seen in Column B (SML,MED, LG, XLG, HUGE):
   =TRANSPOSE(SORT(UNIQUE(FILTER($B$3:$B$101,(($B$3:$B$101="SML")+($B$3:$B$101="MED")+($B$3:$B$101="LG")+($B$3:$B$101="XLG")+($B$3:$B$101="HUGE"))*($A$3:$A$101=$F3),""))))
3. Drag the formula in G3 downward to G8.

But as you can see from Column D, the list of possible size descriptions is prodigious, and I don't want to have to list all of them in the formula, since that would make it unwieldy. Thus I'd instead like to filter based on the array of sizes listed in Column D. I tried the following, in which I asked Excel to filter based on the values in B2:B100 equalling whatever values are in D2:D100, but it didn't work:

=TRANSPOSE(SORT(UNIQUE(FILTER($B$3:$B$1001,(($B$3:$B$101=$D3:$D101)*($A$3:$A$101=$F3)),""))))

And neither did this:

=TRANSPOSE(SORT(UNIQUE(FILTER($B$3:$B$101, ISNUMBER(SEARCH($B$3:$B$101,$D$3:$D$101)*($A$3:$A$101=$F3))))))

How would I do this?

Answer 1

A shorter formula for cell G3:

=IFERROR(TRANSPOSE(FILTER($D$3:$D$16,COUNTIFS($B$3:$B$11,$D$3:$D$16,$A$3:$A$11,F3))),"")

Regards

Amit Tandon

www.excelanytime.com

Answer 2

Formula in cell F3:

=SORT(UNIQUE(FILTER(A4:A15,A4:A15<>"")))

Formula in cell G3 to be copied down:

=IFERROR(TRANSPOSE(FILTER($D$3:$D$16,ISNUMBER(MATCH($D$3:$D$16,FILTER($B$3:$B$11,$A$3:$A$11=F3),0)))),"")

Regards

Amit Tandon

www.excelanytime.com