Alternativa ao uso de ARRAYFORMULA com QUERY ou INDEX
Explicação mais focada:
Esta é a planilha https://docs.google.com/spreadsheets/d/1eMlf9QrI59mdOlUzQSzQherSXxcMbJq9iSyHIxKNaRM/edit?usp=sharing
Na planilha "6-7 Master 2020-21" na coluna ES, cada linha precisa ter um valor proveniente da planilha "SummaryCitizenship". Esse valor está na coluna E da planilha "SummaryCitizenship". A linha na planilha "SummaryCitizenship" de onde o valor vem deve corresponder aos seguintes valores Coluna B ("SummaryCitizenship") dessa linha corresponde à Coluna B ("6-7 Master 2020-21") AND Coluna C = 1 AND Coluna D = Responsabilidade pessoal
Eu poderia colocar essa fórmula em todas as células da coluna ES ("6-7 Master 2020-21")
=QUERY(SummaryCitizenship!A1:E12,"select E where B = '"&B2&"' and C = 1 and D = 'Personal Responsibility' ",0)
e funciona, mas as informações na coluna B ("6-7 Master 2020-21") são dinâmicas e mudarão várias vezes ao dia, principalmente adicionando novas linhas à planilha. Isso significa que eu preciso que a fórmula não esteja em todas as células do ES, mas sim na célula ES1 ou ES2 e afete o resto da planilha como um ARRAYFORMULA faria.
Eu também tentei
=INDEX(FILTER(SummaryCitizenship!$A$2:$E,SummaryCitizenship!$B$2:$B=B1,SummaryCitizenship!$C$2:$C=1,SummaryCitizenship!$D$2:$D="Personal Responsibility"),0,5)
Essa fórmula também funcionará quando colocada em todas as células do ES, mas não funciona com um ARRAYFORMULA
Pergunta antiga que explica com mais detalhes:
Preciso verificar os valores em 'SummaryCitizenship!' planilha em relação a 3 condições e retorna o valor de uma coluna dessa comparação. Posso fazer de 2 maneiras em cada célula; um usando filtro e índice e outro usando consulta. Infelizmente, o número de linhas em '6-7 Master 2020-21!' a planilha muda constantemente, então não posso simplesmente colar a fórmula em todas as células. Essa folha tem mais de 1700 linhas e provavelmente terá perto de 3000 antes do final do ano letivo. Além disso, não sei quando uma nova linha é adicionada, então não posso simplesmente aparecer e adicionar a fórmula quando necessário. Eu realmente preciso de algo que funcione a partir da referência de célula ES2 ou ES1.
Aqui estão as fórmulas que funcionam quando coladas em cada célula:
=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)
Se eu pudesse fazer com que qualquer um deles funcionasse com arrayFormula, eu estaria pronto. Infelizmente, eles não fazem.
Em pseudocódigo, o que eu preciso é: Se a ID exclusiva do aluno (coluna B "6-7 Master 2020-21") corresponder à UniqueID na coluna B de "SummaryCitizenship" e a coluna C de "SummaryCitizenship" do trimestre for 1, e a coluna D do padrão PRIDE "SummaryCitizenship" é "Responsabilidade pessoal", retorne a soma do valor de ajuste de pontos "SummaryCitizenship", coluna C na coluna ES de "6-7 Master 2020-21". Faça isso para todas as linhas de "6-7 Master 2020-21!" coluna ES de preferência com uma entrada de função em ES1 ou ES2.
Não sei muito sobre o GAS, mas posso fazer um pouco com ele. Se você tiver uma solução que inclua GAS, eu ficaria muito grato por isso também.
Respostas
Correto, INDEX()não funciona com ArrayFormula, mas podemos usar o Vlookup para contornar isso.
Isso deve dar a você o que deseja:
=ArrayFormula(IFNA(VLOOKUP(B2:B,FILTER(SummaryCitizenship!B2:E,SummaryCitizenship!C2:C=1,SummaryCitizenship!D2:D="Personal Responsibility"),4,0)))
(Não se esqueça de limpar sua coluna!)
Versão 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
)
)
)