Alternative to using ARRAYFORMULA with QUERY or INDEX

Question

More focused explanation:

This is the spreadsheet https://docs.google.com/spreadsheets/d/1eMlf9QrI59mdOlUzQSzQherSXxcMbJq9iSyHIxKNaRM/edit?usp=sharing

On sheet "6-7 Master 2020-21" in column ES, each row needs to have a value that comes from sheet "SummaryCitizenship". That value is in column E of sheet "SummaryCitizenship". The row in sheet "SummaryCitizenship" that the value comes from should match the following values Column B ("SummaryCitizenship") of that row matches Column B ("6-7 Master 2020-21") AND Column C = 1 AND Column D = Personal Responsibility

I could put this formula into every cell on Column ES ("6-7 Master 2020-21")

=QUERY(SummaryCitizenship!A1:E12,"select E where B = '"&B2&"' and C = 1 and D = 'Personal Responsibility' ",0)

and it works, but the information in Column B ("6-7 Master 2020-21") is dynamic and will change multiple times a day, mostly adding new rows to the sheet. That means I need the formula to not be in every cell in ES, but rather in cell ES1 or ES2 and affect the rest of the sheet like an ARRAYFORMULA would.

I have also tried

=INDEX(FILTER(SummaryCitizenship!$A$2:$E,SummaryCitizenship!$B$2:$B=B1,SummaryCitizenship!$C$2:$C=1,SummaryCitizenship!$D$2:$D="Personal Responsibility"),0,5) 

That formula will also work when placed in every cell of ES, but does not work with an ARRAYFORMULA

Old question that explains in more detail:

I need to check the values in the 'SummaryCitizenship!' sheet against 3 conditions and return one column's value from that comparison. I can do it 2 ways in each cell; one using filter and index and another using query. Unfortunately, the number of rows in '6-7 Master 2020-21!' sheet is constantly changing so I can't just paste the formula into every cell. That sheet has over 1700 rows and will have probably near 3000 before the end of the school year. Also, I don't know when a new row is added, so I can't just pop in and add the formula when needed. I really need something that will work from cell reference ES2 or ES1.

Here are the formulas that work when pasted into each cell:

=INDEX(FILTER(SummaryCitizenship!$A$2:$E,SummaryCitizenship!$B$2:$B=B1,SummaryCitizenship!$C$2:$C=1,SummaryCitizenship!$D$2:$D="Personal Responsibility"),0,5)

=QUERY(SummaryCitizenship!A1:E12,"select E where B = '"&B2&"' and C = 1 and D = 'Personal Responsibility' ",0)

If I could only get either one of those to work with arrayFormula, I would be set. Sadly, they don't.

In pseudo code, what I need is: If the student's unique ID (Column B "6-7 Master 2020-21") matches the UniqueID on sheet "SummaryCitizenship" column B, and the Quarter "SummaryCitizenship" column C is 1, and the PRIDE Standard "SummaryCitizenship" column D is "Personal Responsibility", return the sum Point Adjustment value "SummaryCitizenship" column C into Column ES of "6-7 Master 2020-21". Do that for all rows of "6-7 Master 2020-21!" column ES preferably with one function entry in ES1 or ES2.

I don't know a lot about GAS, but I can do a little with it. If you have a solution that includes GAS, I would be grateful for that as well.

Answer 1

Correct, INDEX() does not work with ArrayFormula, but we can use Vlookup to work around that.

This should get you what you want:

=ArrayFormula(IFNA(VLOOKUP(B2:B,FILTER(SummaryCitizenship!B2:E,SummaryCitizenship!C2:C=1,SummaryCitizenship!D2:D="Personal Responsibility"),4,0)))

(Don't forget to clear your column!)

Marked up version:

=ArrayFormula(
    IFNA(                           // Blank if NA
        VLOOKUP(
            B2:B,                   // Unique ID lookup
            FILTER(                 // Gives us the filter conditions on other table
                SummaryCitizenship!B2:E,    // Key column for VLOOKUP is B
                SummaryCitizenship!C2:C=1,
                SummaryCitizenship!D2:D="Personal Responsibility"
            ),
            4,                      // Index to column 4
            0                       // Exact match
        )
    )
)