Fórmula genérica
=IF(rowcheck,INDEX(data,MATCH(rownum,helper,0),column),"")
Resumo
Uma maneira de extrair várias correspondências no Excel é usar INDEX e MATCH com uma coluna auxiliar que sinaliza os dados correspondentes. Isso evita a complexidade de uma fórmula de matriz mais avançada. No exemplo mostrado, a fórmula em H6 é:
=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")
onde ct (G3), dados (B3: E52) e auxiliar (E3: E52) são intervalos nomeados.
Explicação
O desafio das fórmulas de pesquisa que recuperam mais de uma correspondência é gerenciar duplicatas (ou seja, várias correspondências). Fórmulas de pesquisa como VLOOKUP e INDEX + MATCH podem encontrar facilmente a primeira correspondência, mas é muito mais difícil pesquisar "todas as correspondências" quando os critérios encontram mais de uma correspondência.
Esta fórmula lida com esse desafio usando uma coluna auxiliar que retorna um valor numérico que pode ser usado para extrair facilmente várias correspondências. A fórmula na coluna auxiliar é semelhante a esta:
=SUM(E2,AND(C3=$I$3,D3=$J$3))
A coluna auxiliar testa cada linha nos dados para ver se o Departamento na coluna C corresponde ao valor em I3 e o Edifício na coluna D corresponde ao valor em J3. Ambos os testes lógicos devem retornar TRUE para que AND retorne TRUE.
Para cada linha, o resultado da função AND é adicionado ao "valor acima" na coluna auxiliar para gerar uma contagem. O efeito prático desta fórmula é um contador incremental que só muda quando uma (nova) correspondência é encontrada. Então, o valor permanece o mesmo até que a próxima correspondência seja encontrada. Isso funciona porque os resultados TRUE / FALSE retornados por AND são forçados a valores 1/0 como parte da operação de soma. Os resultados FALSE não acrescentam nada e os resultados TRUE adicionam 1.
De volta à área de extração, a fórmula de pesquisa para Nome na coluna H se parece com isto:
=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")
Trabalhando de dentro para fora, a parte INDEX + MATCH da fórmula procura o nome da primeira correspondência encontrada, usando o número da linha na coluna G como o valor de correspondência:
INDEX(data,MATCH($G6,helper,0),1)
INDEX recebe todas as 3 colunas de dados como a matriz (intervalo denominado "dados") e MATCH é configurado para corresponder ao número da linha dentro da coluna auxiliar (o intervalo nomeado "auxiliar") no modo de correspondência exata (terceiro argumento definido como zero) .
É aqui que a inteligência da fórmula se torna aparente. A coluna auxiliar obviamente contém duplicatas, mas não importa, porque MATCH corresponderá apenas ao primeiro valor. Por design, cada "primeiro valor" corresponde à linha correta na tabela de dados.
As fórmulas nas colunas I e J são iguais a H, exceto para o número da coluna, que é aumentado em cada caso em um.
A instrução IF que envolve a fórmula INDEX / MATCH executa uma função simples - ela verifica cada número de linha na área de extração para ver se o número da linha é menor ou igual ao valor em G3 (intervalo denominado "ct"), que é a contagem total de todos os registros correspondentes. Nesse caso, a lógica INDEX / MATCH é executada. Caso contrário, IF produz uma string vazia ("").
A fórmula em G3 (intervalo denominado "ct") é simples:
=MAX(helper)
Como o valor máximo na coluna auxiliar é igual à contagem total de correspondências, a função MAX é tudo o que precisamos.
Observação: a área de extração precisa ser configurada manualmente para lidar com a quantidade de dados necessária (ou seja, 5 linhas, 10 linhas, 20 linhas, etc.). Neste exemplo, está limitado a 5 linhas apenas para manter a planilha compacta.
Aprendi essa técnica no livro Control + Shift + Enter de Mike Girvin.
A função FILTER
Se você tiver a versão Dynamic Array do Excel, a função FILTER é muito mais fácil de extrair todos os dados correspondentes.
