Pesquisas com vários critérios usando INDEX-MATCH

Às vezes, uma única coluna não identifica uma linha de forma exclusiva. Você pode precisar do preço de um produto em um tamanho específico ou do salário de um funcionário em um departamento específico.

Isso exige uma busca com vários critérios: fazer a correspondência em duas ou mais colunas ao mesmo tempo para identificar exatamente uma linha.

INDEX-MATCH trata isso de forma elegante, combinando as condições em um único teste de correspondência, sem exigir colunas auxiliares adicionais.

O modelo mental mais simples é unir as colunas que formam a chave em uma só. Adicione uma coluna auxiliar que combine produto e tamanho e, depois, faça uma busca comum nela.

Por exemplo, uma célula auxiliar pode conter =A2&"|"&B2, produzindo "Shirt|Large". Depois, você usa MATCH para pesquisar "Shirt|Large" nessa coluna combinada.

Isso funciona, mas deixa sua planilha mais carregada. As próximas etapas mostram como dispensar totalmente a coluna auxiliar.

O truque central é multiplicar os testes das duas condições dentro de MATCH.

(A2:A10=G1) produz uma matriz de TRUE/FALSE para o primeiro critério. (B2:B10=G2) faz o mesmo para o segundo. Ao multiplicá-las, (A2:A10=G1)*(B2:B10=G2) produz 1 apenas onde ambas são TRUE e 0 nos demais casos.

Em seguida, MATCH procura o valor 1 para encontrar a linha que atende às duas condições.

Em planilhas, TRUE se comporta como 1 e FALSE como 0. Multiplicar dois desses valores imita um AND lógico:

• 1 vezes 1 = 1 (as duas condições são atendidas)
• 1 vezes 0 = 0
• 0 vezes 1 = 0
• 0 vezes 0 = 0

Assim, apenas as linhas em que os dois critérios são atendidos produzem 1. Todas as outras linhas se tornam 0. Esse único 1 marca a linha que queremos.

Agora envolva a matriz multiplicada em MATCH, pesquisando o valor exato 1.

MATCH(1, (A2:A10=G1)*(B2:B10=G2), 0) retorna a posição da primeira linha em que as duas condições são TRUE.

Se a combinação correspondente estiver na quarta linha de dados, MATCH retornará 4. Essa é a posição de que INDEX precisa para buscar a resposta.

Forneça esse resultado de MATCH a INDEX sobre a coluna que você realmente deseja, como o preço em C2:C10.

A fórmula completa significa: em C2:C10, retorne o valor da linha em que o produto seja igual a G1 e o tamanho seja igual a G2.

Esta é uma verdadeira busca com vários critérios, sem coluna auxiliar e sem reorganizar seus dados.

Esta fórmula avalia matrizes de condições. No Excel moderno e no Google Sheets, basta pressionar Enter para que ela funcione.

Em versões antigas do Excel — anteriores às matrizes dinâmicas — você deve confirmá-la como uma fórmula de matriz usando Ctrl+Shift+Enter, o que adiciona chaves. Se o resultado estiver errado ou mostrar um erro no Excel antigo, essa etapa de confirmação geralmente é o que está faltando.

Precisa de três critérios? Basta multiplicar por mais um teste. Suponha que você também queira fazer a correspondência com uma cor na coluna D usando a entrada G3.

Cada fator adicional (range=criterion) restringe ainda mais o resultado. Apenas as linhas em que todas as condições são TRUE mantêm um produto igual a 1; qualquer FALSE transforma o produto inteiro em 0.

Esse padrão pode ser ampliado para quantas colunas forem necessárias.

Dados: A = produto, B = tamanho, C = preço. Você quer o preço de uma "Shirt" no tamanho "Large".

• G1 = "Shirt", G2 = "Large".
• As matrizes de condições produzem 1 apenas na linha Shirt+Large, por exemplo, a linha 4.
• MATCH(1, ..., 0) retorna 4.
• INDEX(C2:C10, 4) retorna o preço dessa linha.

Altere qualquer uma das entradas e a fórmula encontrará novamente a linha correta instantaneamente.

Tenha estes pontos em mente:

• Intervalos iguais: cada intervalo de condição e a coluna de INDEX devem ter a mesma altura.
• Nenhuma correspondência: se nenhuma linha atender a todos os critérios, MATCH retornará #N/A. Envolva toda a fórmula em IFERROR.
• Duplicatas: se mais de uma linha corresponder, MATCH retornará apenas a primeira. Torne seus critérios específicos o suficiente para obter uma única correspondência.

Se várias linhas puderem corresponder e o objetivo for totalizar seus valores em vez de buscar apenas um, SUMPRODUCT é uma alternativa simples ao INDEX-MATCH inserido como matriz.

Ele multiplica as matrizes de condições pela coluna de valores e soma os resultados, portanto somente as linhas que atendem aos dois critérios contribuem. Não é necessário usar Ctrl+Shift+Enter, pois SUMPRODUCT lida nativamente com matrizes.

Use INDEX-MATCH para obter um único valor correspondente; use SUMPRODUCT para agregar todas as correspondências.

Teste seus conhecimentos sobre buscas com vários critérios.

Para buscas com vários critérios usando INDEX-MATCH:

• Multiplique as matrizes de condições: (A=G1)*(B=G2) produz 1 somente onde todas as condições são atendidas (um AND lógico).
• MATCH(1, ..., 0) encontra a posição dessa linha.
• INDEX(returnCol, position) retorna o valor.

Adicione mais fatores *(range=criterion) para incluir outras condições, mantenha os intervalos com a mesma altura, confirme com Ctrl+Shift+Enter no Excel legado e proteja a fórmula com IFERROR.