Help with SUMPRODUCT in a large array

Hi there! I tried to include enough info to explain my problem without overloading you. The bold sentences is my question. Thank you in advance!

I've been attempting to build a formua that copies rows from one worksheet to another based on multiple criteria. I've attached a copy of my file with dummy data. My actual file has 900+ rows on the full list. Results are filtered to the new worksheet based on 3 categories and 4 options. Categories have values (blank), A, AB, ABC. Options have values (blank), (mix of +,V,E,G), --. The user can choose an option for a category, like B, and all rows with B in that category are copied. If an option is enabled, all rows containing text (+,V,E,G) are incuded, but all rows containing -- are not included, even if another option or category would otherwise include them.

I am using ISNUMBER(SEARCH()) for each of the categories to search if the user-defined category value matches.

I am attempting to use SUMPRODUCT for the options with the following arrays:
Option enabled true/false: {1,1,1,1} means all are enabled
Option has text (+,V,E,G,--): {1,1,1,1} means all have text
option does not have -- text: {1,1,1,1} means it does not have --, {-9,-9,-9,-9} means it does have --. (I explain the -9 below)

That SUMPRODUCT formua works if I write it by itself and apply it to a single row, but when I put it into the INDEX array formula, it doesn't work because the second array creates a 900x4 array rather than checking one row at a time. I'm not sure how to fix this. Please help!


Some more details:

The logic I am using to choose which rows should be copied to the filtered worksheet is

( Category 1 matches OR Category 2 matches OR category 3 matches OR Option 1 Adds OR Option 2 Adds OR Option 3 Adds OR Option 4 Adds ) AND (Enabled Option Does Not Remove)

I can't use logical OR/AND functions since this is an array formula, so I am using addition and multiplication to achieve this and wrapping it in a single IF statement. If I have a category match or an option adds a row, I add 1. If an option removes a row, I subtract 9. Then check IF(sum>0). This actually comes out as (Cat1 + Cat2 + Cat3 + (Opt1 + Opt2 + Opt3 + Opt4) where the possible values are 0, 1 or -9.

Well, I'm still interested in the solution because right now I have a hugely messy function, but this works! I imagine there is a way to use SUMPRODUCT or something similar to do the category comparison and the option comparison as groups, but it turns out I can do them all individually and achieve the desired result, albeit with a very messy equation. Here's my fixed equation for the sample file:

=IFERROR(INDEX('Full List'!B$10:B$25,SMALL(IF((ISNUMBER(SEARCH($D$2,'Full List'!$F$10:$F$25))+ISNUMBER(SEARCH($D$3,'Full List'!$G$10:$G$25))+ISNUMBER(SEARCH($D$4,'Full List'!$H$10:$H$25))+IF($G$2="Yes",1,0)*ISTEXT('Full List'!$I$10:$I$25)*IF('Full List'!$I$10:$I$25="--",-9,1)+IF($G$3="Yes",1,0)*ISTEXT('Full List'!$J$10:$J$25)*IF('Full List'!$J$10:$J$25="--",-9,1)+IF($G$4="Yes",1,0)*ISTEXT('Full List'!$K$10:$K$25)*IF('Full List'!$K$10:$K$25="--",-9,1)+IF($G$5="Yes",1,0)*ISTEXT('Full List'!$L$10:$L$25)*IF('Full List'!$L$10:$L$25="--",-9,1))>0,ROW('Full List'!B$10:B$25)-ROW('Full List'!A$9)),ROW()-ROW($N$9))),"")