ÍNDICE e CORRESP com várias matrizes
Suponha que você tenha várias tabelas com as mesmas legendas, conforme mostrado abaixo. Procurar valores que correspondam aos critérios fornecidos nessas tabelas pode ser uma tarefa difícil para você. Neste tutorial, vamos falar sobre como procurar um valor em várias matrizes, intervalos ou grupos combinando critérios específicos com as funções ÍNDICE, CORRESP e ESCOLHER.
Como pesquisar um valor em várias matrizes?
Para descobrir os líderes de diferentes grupos que pertencem a diferentes departamentos, você pode primeiro usar a função ESCOLHER para direcionar a tabela da qual deseja retornar o nome do líder. A função CORRESP encontrará então a posição do líder na tabela à qual ele/ela pertence. Por fim, a função ÍNDICE recuperará o líder com base nas informações de posição mais a coluna específica onde os nomes dos líderes estão listados.
Sintaxe genérica
=ÍNDICE(ESCOLHER(número_matriz,matriz1,matriz2,…),CORRESP(valor_procura,intervalo_procura,0),número_coluna)
- número_matriz: O número que ESCOLHER usa para indicar uma matriz da lista matriz1, matriz2,… para retornar o resultado.
- matriz1, matriz2,…: As matrizes das quais retornar o resultado. Aqui se referem às três tabelas.
- valor_procura: O valor que a fórmula combinada usa para encontrar a posição do seu líder correspondente. Aqui se refere ao grupo dado.
- intervalo_procura: O intervalo de células onde o valor_procura está listado. Aqui se refere ao intervalo de grupos. Observação: Você pode usar o intervalo de grupos de qualquer departamento, já que eles são todos iguais e só precisamos obter o número da posição.
- número_coluna: A coluna que você indica de onde deseja recuperar os dados.
Para saber o líder do Grupo D que pertence ao Departamento A, copie ou insira a fórmula abaixo na célula G5 e pressione Enter para obter o resultado:
=ÍNDICE(ESCOLHER(1,$B$5:$C$8,,$B$11:$C$14,,$B$17:$C$20),CORRESP(F5,$B$5:$B$8,0),2)
√ Nota: Os sinais de dólar ($) acima indicam referências absolutas, o que significa que os intervalos de nomes e classes na fórmula não mudarão quando você mover ou copiar a fórmula para outras células. Depois de inserir a fórmula, arraste a alça de preenchimento para baixo para aplicar a fórmula às células abaixo e altere o número_matriz de acordo.
Explicação da fórmula
=ÍNDICE(ESCOLHER(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),CORRESP(F5,$B$5:$B$8,0),2)
- ESCOLHER(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20): A função ESCOLHER retorna a primeira matriz das três matrizes listadas na fórmula. Então, ela retornará $B$5:$C$8, ou seja, o intervalo de dados do Departamento A.
- CORRESP(F5,$B$5:$B$8,0): O tipo_de_correspondência 0 força a função CORRESP a retornar a posição da primeira correspondência do Grupo D, o valor na célula F5, no intervalo $B$5:$B$8, que é 4.
- ÍNDICE(ESCOLHER(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),CORRESP(F5,$B$5:$B$8,0),22) = ÍNDICE($B$5:$C$8,4,22): A função ÍNDICE recupera o valor na interseção da 4ª linha e 2ª coluna do intervalo $B$5:$C$8, que é Emily.
Para evitar alterar o número_matriz na fórmula toda vez que você a copiar, você pode usar a coluna auxiliar, a coluna D. A fórmula seria assim:
=ÍNDICE(ESCOLHER(D5,$B$5:$C$8,,$B$11:$C$14,,$B$17:$C$20),CORRESP(F5,$B$5:$B$8,0),2)
√ Nota: Os números 1, 2, 3 na coluna auxiliar indicam matriz1, matriz2, matriz3 dentro da função ESCOLHER.
Funções relacionadas
A função ÍNDICE do Excel retorna o valor exibido com base em uma posição dada de um intervalo ou matriz.
A função CORRESP do Excel pesquisa um valor específico em um intervalo de células e retorna a posição relativa do valor.
A função ESCOLHER retorna um valor da lista de argumentos de valor com base no número de índice fornecido. Por exemplo, ESCOLHER(3,”Maçã”,”Pêssego”,”Laranja”) retorna Laranja, o número de índice é 3, e Laranja é o terceiro valor após o número de índice na função.
Fórmulas relacionadas
Pesquisar valores de outra planilha ou pasta de trabalho
Se você sabe como usar a função PROCV para pesquisar valores em uma planilha, buscar valores de outra planilha ou pasta de trabalho não será um problema para você.
PROCV com nome de planilha dinâmico
Em muitos casos, você pode precisar coletar dados de várias planilhas para resumir. Com a combinação da função PROCV e da função INDIRETO, você pode criar uma fórmula para pesquisar valores específicos em planilhas com nome de planilha dinâmico.
Pesquisa de múltiplos critérios com ÍNDICE e CORRESP
Ao lidar com um grande banco de dados em uma planilha do Excel com várias colunas e linhas de legenda, sempre é complicado encontrar algo que atenda a vários critérios. Nesse caso, você pode usar uma fórmula de matriz com as funções ÍNDICE e CORRESP.
As Melhores Ferramentas de Produtividade para o Office
Kutools para Excel - Ajuda Você a Se Destacar na Multidão
Kutools para Excel Conta com Mais de 300 Funcionalidades, Garantindo Que O Que Você Precisa Está Apenas Um Clique de Distância...
Office Tab - Ative a Leitura e Edição com Guias no Microsoft Office (inclui Excel)
- Um segundo para alternar entre dezenas de documentos abertos!
- Reduz centenas de cliques de mouse para você todos os dias, diga adeus à mão do mouse.
- Aumenta sua produtividade em 50% ao visualizar e editar vários documentos.
- Traz Guias Eficientes para o Office (inclui Excel), Assim Como Chrome, Edge e Firefox.