ÍNDICE e CORRESP em múltiplas colunas

Para procurar um valor correspondendo-o em múltiplas colunas, uma fórmula de matriz baseada nas funções ÍNDICE e CORRESP, que incorpora as funções MMULT, TRANSPOR e COL, fará o trabalho por si.

Como procurar um valor correspondendo-o em múltiplas colunas?

Para preencher a turma correspondente de cada aluno, conforme mostrado na tabela acima — em que a informação está distribuída por múltiplas colunas —, comece por utilizar o truque das funções MMULT, TRANSPOR e COL para gerar uma matriz. Depois, a função CORRESP fornecer-lhe-á a posição do valor que procura, que será passada à função ÍNDICE para recuperar o valor pretendido na matriz.

Sintaxe genérica

=INDEX(return_range,(MATCH(1,MMULT(--(lookup_array=lookup_value),TRANSPOSE(COLUMN(lookup_array)^0)),0)))

√ Nota: Trata-se de uma fórmula de matriz que requer que prima Ctrl+Shift+Enter.

Para encontrar a turma do Jimmy, copie ou introduza a fórmula abaixo na célula H5 e prima Ctrl+Shift+Enterpara obter o resultado:

=ÍNDICE()$B$5:$B$7,(CORRESP(1,MMULT(--())))$C$5:$E$7=G5),TRANSPOR(COL()$C$5:$E$7)^0)),0)))

√ Nota: Os sinais de dólar ($) acima indicam referências absolutas, o que significa que os intervalos de nome e classe na fórmula não mudarão quando mover ou copiar a fórmula para outras células. Note que não deve adicionar sinais de dólar à referência de célula que representa o valor de procura, uma vez que pretende que esta seja relativa ao copiá-la para outras células. Após introduzir a fórmula, arraste a alça de preenchimento para baixo para aplicar a fórmula às células seguintes.

Explicação da fórmula

=INDEX($B$5:$B$7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0)))

• --($C$5:$E$7=G5): Este segmento compara cada valor no intervalo $C$5:$E$7 com o valor da célula G5, gerando uma matriz de VERDADEIRO e FALSO como esta:
  {VERDADEIRO,FALSO,FALSO;FALSO,FALSO,FALSO;FALSO,FALSO,FALSO}.
  O duplo sinal negativo converte, em seguida, os valores VERDADEIRO e FALSO em 1 e 0, resultando numa matriz como esta:
  {1,0,0;0,0,0;0,0,0}.
• COLUMN($C$5:$E$7): A função COL retorna os números das colunas do intervalo $C$5:$E$7 numa matriz como esta: {3,4,5}.
• TRANSPOR()COLUMN($C$5:$E$7)^0)=TRANSPOR(){3,4,5}^0):Ao elevar à potência 0, todos os números na matriz {3,4,5} são convertidos em 1: {1,1,1}. A função TRANSPOR converte, de seguida, a matriz de coluna numa matriz de linha como esta:{1;1;1}.
• MMULT()--($C$5:$E$7=G5),TRANSPOR()COLUMN($C$5:$E$7)^0))=MMULT(){1,0,0;0,0,0;0,0,0},{1;1;1}):A função MMULT devolve o produto matricial das duas matrizes, resultando em:{1;0;0}.
• CORRESP(1,)MMULT()--($C$5:$E$7=G5),TRANSPOR()COLUMN($C$5:$E$7)^0)),0)=CORRESP(1,){1;0;0},0):O argumento tipo_de_correspondência 0 obriga a função CORRESP a devolver a posição da primeira ocorrência de 1 na matriz {1;0;0}, que é 1.
• ÍNDICE()$B$5:$B$7,(CORRESP(1,))MMULT()--($C$5:$E$7=G5),TRANSPOR()COLUMN($C$5:$E$7)^0)),0))) = ÍNDICE($B$5:$B$7 A função ÍNDICE devolve o 1.º valor no intervalo das turmas $B$5:$B$7, que é A.

Para procurar facilmente um valor correspondendo em várias colunas, também pode utilizar o nosso suplemento profissional para Excel Kutools for Excel.Veja aqui as instruções para concluir esta tarefa.

Funções relacionadas

Fórmulas relacionadas