ÍNDICE e CORRESP em várias colunas
Para procurar um valor correspondendo em várias colunas, uma fórmula de matriz baseada nas funções ÍNDICE e CORRESP que incorpora MMULT, TRANSPOR e COLUNA fará o trabalho.
Como procurar um valor correspondendo em várias colunas?
Para preencher a classe correspondente de cada aluno conforme mostrado na tabela acima, onde as informações estão listadas em várias colunas, você pode primeiro usar o truque das funções MMULT, TRANSPOR e COLUNA para produzir uma matriz. Em seguida, a função CORRESP fornecerá a posição do seu valor de pesquisa, que será usada pelo ÍNDICE para recuperar o valor que você está procurando na matriz.
Sintaxe Genérica
=ÍNDICE(intervalo_de_retorno;(CORRESP(1;MMULT(--(matriz_de_pesquisa=valor_de_pesquisa);TRANSPOR(COLUNA(matriz_de_pesquisa)^0));0)))
√ Nota: Esta é uma fórmula de matriz que requer que você a insira com Ctrl + Shift + Enter.
- intervalo_de_retorno: O intervalo onde você deseja que a fórmula retorne as informações da classe. Aqui refere-se ao intervalo de classes.
- valor_de_pesquisa: O valor que a fórmula usa para encontrar sua informação de classe correspondente. Aqui refere-se ao nome dado.
- matriz_de_pesquisa: O intervalo de células onde o valor_de_pesquisa está listado; O intervalo com os valores para comparar com o valor_de_pesquisa. Aqui refere-se ao intervalo de nomes.
- tipo_de_correspondência 0: Força a função CORRESP a encontrar o primeiro valor que seja exatamente igual ao valor_de_pesquisa.
Para encontrar a classe de Jimmy, copie ou insira a fórmula abaixo na célula H5 e pressione Ctrl + Shift + Enter para obter o resultado:
=ÍNDICE($B$5:$B$7;(CORRESP(1;MMULT(--($C$5:$E$7=G5);TRANSPOR(COLUNA($C$5:$E$7)^0));0)))
√ 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. Observe que você não deve adicionar sinais de dólar à referência de célula que representa o valor de pesquisa, pois você quer que ela seja relativa quando você a copiar 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.
Explicação da fórmula
=ÍNDICE($B$5:$B$7;(CORRESP(1;MMULT(--($C$5:$E$7=G5);TRANSPOR(COLUNA($C$5:$E$7)^0));0)))
- --($C$5:$E$7=G5): Este segmento verifica cada valor no intervalo $C$5:$E$7 se eles são iguais ao valor na célula G5 e gera uma matriz VERDADEIRO e FALSO como esta:
{TRUE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE}.
O duplo negativo então converterá os VERDADEIROS e FALSOS em 1s e 0s para gerar uma matriz como esta:
{1,0,0;0,0,0;0,0,0}. - COLUNA($C$5:$E$7): A função COLUNA retorna os números das colunas para o intervalo $C$5:$E$7 em uma matriz como esta: {3,4,5}.
- TRANSPOR(COLUNA($C$5:$E$7)^0) = TRANSPOR({3,4,5}^0): Após elevar à potência 0, todos os números na matriz {3,4,5} serão convertidos para 1: {1,1,1}. A função TRANSPOR então converte a matriz de coluna em uma matriz de linha assim: {1;1;1}.
- MMULT(MMULT(--($C$5:$E$7=G5),,TRANSPOR(COLUNA($C$5:$E$7)^0))) = MMULT(MMULT({1,0,0;0,0,0;0,0,0},,{1;1;1})): A função MMULT retorna o produto matricial das duas matrizes assim: {1;0;0}.
- CORRESP(1,MMULT(--($C$5:$E$7=G5),TRANSPOR(COLUNA($C$5:$E$7)^0)),0) = CORRESP(1,{1;0;0},0): O tipo_de_correspondência 0 força a função CORRESP a retornar a posição da primeira correspondência de 1 na matriz {1;0;0}, que é 1.
- ÍNDICE($B$5:$B$7$B$5:$B$7,,,(CORRESP(1,MMULT(--($C$5:$E$7=G5),,,TRANSPOR(COLUNA($C$5:$E$7)^0)),0))) = ÍNDICE($B$5:$B$7$B$5:$B$7,,,1): A função ÍNDICE retorna o 1º valor no intervalo de classes $B$5:$B$7, que é A.
Para procurar facilmente um valor correspondendo em várias colunas, você também pode usar nosso complemento profissional Excel Kutools Para Excel. Veja as instruções aqui para realizar a missão.
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 procura um valor específico em um intervalo de células e retorna a posição relativa do valor.
A função MMULT do Excel retorna o produto matricial de duas matrizes. O resultado da matriz tem o mesmo número de linhas que a matriz1 e o mesmo número de colunas que a matriz2.
A função TRANSPOR do Excel altera a orientação de um intervalo ou matriz. Por exemplo, ela pode rotacionar uma tabela disposta horizontalmente em linhas para verticalmente em colunas ou vice-versa.
A função COLUNA retorna o número da coluna onde a fórmula aparece ou retorna o número da coluna da referência dada. Por exemplo, a fórmula =COLUNA(BD) retorna 56.
Fórmulas relacionadas
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 legendas de linha, sempre é complicado encontrar algo que atenda a múltiplos critérios. Nesse caso, você pode usar uma fórmula de matriz com as funções ÍNDICE e CORRESP.
Pesquisa bidirecional com ÍNDICE e CORRESP
Para pesquisar algo tanto nas linhas quanto nas colunas no Excel, ou seja, para localizar um valor na interseção de uma linha e coluna específicas, podemos usar a ajuda das funções ÍNDICE e CORRESP.
Pesquisa do valor de correspondência mais próxima com múltiplos critérios
Em alguns casos, você pode precisar procurar o valor de correspondência mais próxima ou aproximada com base em mais de um critério. Com a combinação das funções ÍNDICE, CORRESP e SE, você pode fazer isso rapidamente no Excel.
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.