Procura com múltiplos critérios com ÍNDICE e CORRESP

Ao trabalhar com uma base de dados extensa numa folha de cálculo do Excel, com várias colunas e legendas de linha, encontrar algo que satisfaça múltiplos critérios pode ser sempre complicado. Neste caso, pode utilizar uma fórmula de matriz com as funções ÍNDICE e CORRESP.

Como realizar uma pesquisa com múltiplos critérios?

Para identificar o produto que é branco, de tamanho médio e com um preço de $18, conforme ilustrado na imagem acima, pode utilizar a lógica booleana para gerar uma matriz de 1s e 0s que indique as linhas que satisfazem todos os critérios. A função CORRESP encontrará então a posição da primeira linha que cumpre todas as condições, e a função ÍNDICE recuperará o ID do produto correspondente nessa mesma linha.

Sintaxe genérica

=INDEX(return_range,MATCH(1,(criteria_value1=criteria_range1*criteria_value2=criteria_range2*(…),0))

√ Nota: Esta é uma fórmula de matriz que requer que a introduza com Ctrl+Shift+Enter.

Para encontrar o produto que é brancoe de tamanho médiocom um preço de $18, copie ou introduza a fórmula abaixo na célula H8 e prima Ctrl+Shift+Enterpara obter o resultado:

=ÍNDICE()B5:B10,CORRESP(1,())«Branco»=C5:C10)*(«Médio»=D5:D10)*(18=E5:E10);0))

Ou, utilize Uma referência de célula para tornar a fórmula dinâmica:

=ÍNDICE()B5:B10;CORRESP(1;())H3=C5:C10)*(H5=D5:D10)*(H6=E5:E10);0))

Explicação da fórmula

=INDEX(B5:B10,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))

• (H3=C5:C10)*(H5=D5:D10)*(H6=E5:E10): A fórmula compara a cor na célula H3 com todas as cores no intervalo C5:C10; compara o tamanho em H5 com todos os tamanhos em D5:D10; e compara o preço em H6 com todos os preços em E5:E10. O resultado inicial é algo como isto:
  {VERDADEIRO;FALSO;VERDADEIRO;FALSO;VERDADEIRO;FALSO}*{FALSO;FALSO;VERDADEIRO;VERDADEIRO;VERDADEIRO;FALSO}*{FALSO;FALSO;FALSO;VERDADEIRO;VERDADEIRO;FALSO}.
  A multiplicação converte os valores VERDADEIRO e FALSO em 1 e 0, respetivamente:
  {1;0;1;0;1;0}*{0;0;1;1;1;0}*{0;0;0;1;1;0}.
  Após a multiplicação, obtemos uma única matriz como esta:
  {0;0;0;0;1;0}.
• CORRESP(1,)(H3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0)=CORRESP(1,) O tipo_correspondência 0 indica à função CORRESP que encontre uma correspondência exata. A função devolve então a posição de 1 na matriz {0;0;0;0;1;0}, que é 5.
• ÍNDICE()B5:B10,CORRESP(1,)(H3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0)) = ÍNDICE(B5:B10 A função ÍNDICE devolve o 5.º valor na gama de IDs dos produtos B5:B10, que é 30005.

Funções relacionadas

Fórmulas relacionadas