Alternativa al uso de ARRAYFORMULA con QUERY o INDEX

Question

Explicación más enfocada:

Esta es la hoja de calculo https://docs.google.com/spreadsheets/d/1eMlf9QrI59mdOlUzQSzQherSXxcMbJq9iSyHIxKNaRM/edit?usp=sharing

En la hoja "6-7 Master 2020-21" en la columna ES, cada fila debe tener un valor que proviene de la hoja "SummaryCitizenship". Ese valor está en la columna E de la hoja "SummaryCitizenship". La fila en la hoja "SummaryCitizenship" de la que proviene el valor debe coincidir con los siguientes valores. La columna B ("SummaryCitizenship") de esa fila coincide con la columna B ("6-7 Master 2020-21") Y la columna C = 1 Y la columna D = Responsabilidad personal

Podría poner esta fórmula en cada celda de la Columna ES ("6-7 Master 2020-21")

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

y funciona, pero la información en la Columna B ("6-7 Master 2020-21") es dinámica y cambiará varias veces al día, principalmente agregando nuevas filas a la hoja. Eso significa que necesito que la fórmula no esté en todas las celdas de ES, sino en la celda ES1 o ES2 y afecte al resto de la hoja como lo haría una ARRAYFORMULA.

Yo tambien he probado

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

Esa fórmula también funcionará cuando se coloque en cada celda de ES, pero no funciona con ARRAYFORMULA

Antigua pregunta que explica con más detalle:

Necesito comprobar los valores en el 'SummaryCitizenship!' hoja contra 3 condiciones y devuelve el valor de una columna de esa comparación. Puedo hacerlo de 2 formas en cada celda; uno usando filtro e índice y otro usando consulta. Desafortunadamente, el número de filas en '6-7 Master 2020-21!' La hoja cambia constantemente, por lo que no puedo pegar la fórmula en cada celda. Esa hoja tiene más de 1700 filas y probablemente tendrá cerca de 3000 antes de que termine el año escolar. Además, no sé cuándo se agrega una nueva fila, por lo que no puedo simplemente aparecer y agregar la fórmula cuando sea necesario. Realmente necesito algo que funcione desde la referencia de celda ES2 o ES1.

Aquí están las fórmulas que funcionan cuando se pegan en cada celda:

=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)

Si solo pudiera hacer que cualquiera de esos funcionara con arrayFormula, estaría listo. Lamentablemente, no lo hacen.

En pseudocódigo, lo que necesito es: Si la identificación única del estudiante (columna B "6-7 Master 2020-21") coincide con el UniqueID en la columna B de la hoja "SummaryCitizenship", y la columna C del "SummaryCitizenship" del trimestre es 1, y la columna D "SummaryCitizenship" del estándar PRIDE es "Responsabilidad personal", devuelva la suma del valor de ajuste de puntos "SummaryCitizenship" columna C en la columna ES de "6-7 Master 2020-21". Haga eso para todas las filas de "6-7 Master 2020-21!" columna ES preferiblemente con una entrada de función en ES1 o ES2.

No sé mucho sobre GAS, pero puedo hacer un poco con él. Si tiene una solución que incluye GAS, también se lo agradecería.

Answer 1

Correcto, INDEX()no funciona con ArrayFormula, pero podemos usar Vlookup para solucionarlo .

Esto debería darte lo que quieres:

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

(¡No olvide limpiar su columna!)

Versión marcada:

=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
        )
    )
)