Í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.

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

Fórmulas relacionadas