INDEX e MATCH com várias matrizes
Digamos que você tenha várias tabelas com as mesmas legendas mostradas a seguir; pesquisar valores que correspondam aos critérios fornecidos dessas tabelas pode ser uma tarefa difícil para você. Neste tutorial, falaremos sobre como pesquisar um valor em vários arrays, intervalos ou grupos, combinando critérios específicos com o ÍNDICE, JOGO e ESCOLHE funções.
Como pesquisar um valor em várias matrizes?
Para conhecer o líderes de diferentes grupos que pertencem a diferentes departamentos, você pode primeiro usar a função CHOOSE para direcionar a tabela a partir da qual retornar o nome do líder. A função MATCH irá então descobrir a posição do líder na mesa a que pertence. Finalmente, a função INDEX irá recuperar o líder com base nas informações da posição mais a coluna específica onde os nomes dos líderes estão listados.
Sintaxe genérica
=INDEX(CHOOSE(array_num,array1,array2,…),MATCH(lookup_value,lookup_array,0),column_num)
- núm_array: O número CHOOSE usado para indicar uma matriz da lista matriz1, matriz2, ... para retornar o resultado.
- matriz1, matriz2,…: As matrizes das quais retornar o resultado. Aqui se refere às três tabelas.
- valor_pesquisa: O valor que a fórmula de combinação usou para encontrar a posição de seu líder correspondente. Aqui se refere ao grupo fornecido.
- lookup_array: O intervalo de células onde o lookup_value está listado. Aqui se refere ao intervalo do grupo. Nota: Você pode usar o intervalo de grupo de qualquer departamento, pois eles são todos iguais e só precisamos obter o número da posição.
- núm_coluna: A coluna que você indica da qual deseja recuperar os dados.
Para conhecer o líder do Grupo D que pertence ao Departamento A, copie ou insira a fórmula abaixo na célula G5 e pressione Entrar para obter o resultado:
= ÍNDICE (ESCOLHER (1,$ B $ 5: $ C $ 8,$ B $ 11: $ C $ 14,$ B $ 17: $ C $ 20),PARTIDA(F5,$ B $ 5: $ B $ 8, 0),2)
√ Nota: Os cifrões ($) acima indicam referências absolutas, o que significa que os intervalos de nome e classe na fórmula não serão alterados 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, em seguida, altere o núm_array adequadamente.
Explicação da fórmula
=INDEX(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)
- CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20): A função CHOOSE retorna o 1st array dos três arrays listados na fórmula. Então vai voltar $ B $ 5: $ C $ 8, ou seja, o intervalo de dados do Departamento A.
- CORRESPONDÊNCIA (F5, $ B $ 5: $ B $ 8,0): O match_type 0 força a função MATCH a retornar a posição da primeira combinação de Grupo D, o valor na célula F5, na matriz $ B $ 5: $ B $ 8, o que é 4.
- ÍNDICE(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),CORRESPONDÊNCIA (F5, $ B $ 5: $ B $ 8,0),2) = ÍNDICE ($ B $ 5: $ C $ 8,4,2): A função INDEX recupera o valor na interseção do 4a linha e 2nd coluna do intervalo $ B $ 5: $ C $ 8, o que é Emily.
Para evitar a mudança núm_array na fórmula sempre que copiá-la, 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),PARTIDA(F5,$ B $ 5: $ B $ 8, 0),2)
√ Nota: os números 1, 2, 3 na coluna auxiliar, indique o array1, array2, array3 dentro da função CHOOSE.
Funções relacionadas
A função INDEX do Excel retorna o valor exibido com base em uma determinada posição de um intervalo ou matriz.
A função MATCH do Excel procura um valor específico em um intervalo de células e retorna a posição relativa do valor.
A função CHOOSE retorna um valor da lista de argumentos de valor com base no número de índice fornecido. Por exemplo, CHOOSE (3, ”Maçã”, ”Pêssego”, ”Laranja”) retorna Laranja, o número do índice é 3 e Laranja é o terceiro valor após o número do índice na função.
Fórmulas Relacionadas
Valores de pesquisa de outra planilha ou pasta de trabalho
Se você sabe como usar a função VLOOKUP para pesquisar valores em uma planilha, os valores vlookup de outra planilha ou pasta de trabalho não serão um problema para você.
Vlookup com nome de folha dinâmica
Em muitos casos, pode ser necessário coletar dados em várias planilhas para um resumo. Com a combinação da função VLOOKUP 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 critérios múltiplos com INDEX e MATCH
Ao lidar com um grande banco de dados em uma planilha do Excel com várias colunas e legendas de linha, é sempre difícil encontrar algo que atenda a vários critérios. Nesse caso, você pode usar uma fórmula de matriz com as funções INDEX e MATCH.
As melhores ferramentas de produtividade para escritório
Kutools para Excel - ajuda você a se destacar na multidão
Kutools para Excel possui mais de 300 recursos, Garantindo que o que você precisa está a apenas um clique de distância...
Guia Office - Habilite leitura e edição com guias no Microsoft Office (inclui Excel)
- Um segundo para alternar entre dezenas de documentos abertos!
- Reduza centenas de cliques do 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.
