Como encontrar um valor com base em dois ou mais critérios no Excel?

Imagine que está a trabalhar com uma tabela de vendas de fruta semelhante à apresentada abaixo. Poderá precisar de encontrar o montante de vendas com base em várias informações, como o tipo de fruta, a data de venda e o peso. Ao utilizar fórmulas de matriz no Excel, consegue obter esses valores de forma eficiente, mesmo quando tem de cumprir múltiplas condições. Este método é flexível e adapta-se facilmente a conjuntos de dados nos quais precisa de identificar um único valor que corresponda a vários critérios.

Fórmula de matriz 1: Encontrar valor com dois ou vários critérios no Excel

A estrutura geral desta fórmula de matriz é a seguinte:

{=INDEX(array,MATCH(1,(criteria1=lookup_array1)*(criteria2= lookup_array2)…*(criteria n= lookup_array n),0))}

Por exemplo, se quiser encontrar o montante de vendas de mangavendida em 9/3/2019, introduza a seguinte fórmula numa célula vazia e prima Ctrl+Shift+Enterpara confirmá-la como uma fórmula de matriz:

=INDEX(F3:F22,MATCH(1,(J3=B3:B22)*(J4=C3:C22),0))

Nota: Neste exemplo,

• F3:F22 é a coluna «Amount» (Montante) da qual pretende obter o valor.
• B3:B22 é a coluna «Date» (Data); C3:C22 é a coluna «Fruit» (Fruta).
• J3 é a data escolhida como primeiro critério; J4 é o nome da fruta utilizado como segundo critério.

Adicionar mais critérios é simples. Por exemplo, para procurar o montante de vendas de manga em 9/3/2019 com um peso de 211, basta adicionar a terceira condição tanto no MATCH como nos intervalos de procura, conforme indicado:

=INDEX(F3:F22,MATCH(1,(J3=B3:B22)*(J4=C3:C22)*(J5=E3:E22),0))

Após introduzir a fórmula, prima novamente Ctrl+Shift+Enter para confirmar. O resultado será o montante de vendas que satisfaz todos os critérios especificados.

Fórmula de matriz 2: Encontrar valor com dois ou vários critérios no Excel por concatenação

Alternativamente, pode usar concatenação na sua fórmula para uma abordagem diferente, especialmente se pretender uma estrutura mais compacta. A fórmula básica é:

=INDEX(array,MATCH(criteria1& criteria2…& criteriaN, lookup_array1& lookup_array2…& lookup_arrayN,0),0)

Por exemplo, para obter o montante de vendas de uma fruta com peso de 242em 9/1/2019:

=INDEX(F3:F22,MATCH(J3&J4,B3:B22&C3:C22,0),0)

Nota: Aqui,

• F3:F22 é a coluna Montante; B3:B22 é a Data; E3:E22 é a coluna Peso.
• J3 é a data; J5 é o valor do peso para os seus critérios.

Para mais de dois critérios, expanda tanto os critérios como os intervalos de procura pela mesma ordem:

=INDEX(F3:F22,MATCH(J3&J4&J5,B3:B22&C3:C22&E3:E22,0),0)

Tal como anteriormente, prima Ctrl + Shift + Enter para obter o resultado correto.

Ambos os métodos de fórmula de matriz permitem-lhe encontrar o primeiro valor que satisfaz todos os seus critérios. Contudo, exigem que os intervalos de células tenham o mesmo tamanho e não devolvem várias correspondências — apenas a primeira é recuperada. Se não houver correspondência, a fórmula devolve um erro #N/D. Caso pretenda uma fórmula que mostre todas as correspondências, explore a função FILTER (veja abaixo para mais detalhes).

Algumas dicas práticas e observações:

• Se estiver a trabalhar com versões mais recentes do Excel (Microsoft 365, Excel 2021), pode utilizar fórmulas de matriz dinâmica e a função FILTER para simplificar este processo.
• Para evitar erros #N/D quando não existem correspondências, pode envolver a fórmula na função IFERROR, por exemplo: =IFERROR(INDEX(F3:F22,MATCH(1,(J3=B3:B22)*(J4=C3:C22),0)),«Not found»).
• Verifique cuidadosamente se as células dos critérios de pesquisa não contêm espaços extra nem tipos de dados diferentes.
• Se obtiver um erro após premir apenas Enter, certifique-se de utilizar Ctrl+Shift+Enter para confirmar como uma fórmula de matriz (para Excel 2019 e versões anteriores).

Além de utilizar fórmulas, o Excel disponibiliza a funcionalidade Filtro Avançado, que lhe permite filtrar e extrair todas as linhas que satisfazem dois ou mais critérios, apresentando os resultados noutra localização. Esta abordagem é especialmente útil quando pretende visualizar todos os registos que cumprem as suas condições especificadas, em vez de obter apenas um único valor. Eis como utilizá-la:

1. Aceda ao separador Dados e selecione Avançado no grupo Ordenar e Filtrar para abrir a caixa de diálogo Filtro Avançado.

2. Na caixa de diálogo Filtro Avançado, configure as seguintes definições:
(1) Selecione Copiar para outra localização na secção Ação.
(2) Em Intervalo da lista, selecione o intervalo que contém os dados a filtrar ()A1:E21 neste exemplo).
(3) Em Intervalo dos critérios, selecione o intervalo que contém as suas condições de filtro ()H1:J2 neste caso). Certifique-se de que os cabeçalhos deste intervalo correspondem exatamente aos da sua tabela de dados.
(4) Em Copiar para, selecione a primeira célula onde pretende colar os resultados filtrados ()H9 neste caso).

3. Clique em OK para executar a filtragem.

As linhas que cumprirem todas as condições definidas na sua gama de critérios serão copiadas para a área de destino que especificou. Esta funcionalidade é especialmente útil para rever ou gerar relatórios de todos os registos que correspondem a múltiplas condições de filtro de uma só vez.

Algumas dicas e precauções:

• Certifique-se de que os cabeçalhos do intervalo de critérios sejam idênticos aos da sua tabela principal de dados; caso contrário, o filtro poderá não funcionar corretamente.
• O Filtro Avançado suporta condições E e OU: ao colocar critérios na mesma linha, aplica-se a lógica E (todos devem ser verdadeiros); ao utilizar linhas separadas, aplica-se a lógica OU (basta que um seja verdadeiro).
• O Filtro Avançado não se atualiza dinamicamente quando os seus dados são alterados; é necessário reaplicar o filtro após atualizar os dados ou os critérios.
• Tenha em atenção que as células vazias no intervalo de critérios podem ser interpretadas como «corresponder a qualquer valor» para esse campo.

Em comparação com soluções baseadas em fórmulas, o Filtro Avançado é particularmente adequado para extrair conjuntos de dados completos que correspondem aos critérios, em vez de apenas uma célula. Contudo, não é adequado para pesquisas em tempo real ou frequentemente atualizadas, pois é necessário reexecutar o filtro após alterações nos dados.

Se estiver a utilizar uma versão recente do Excel (Microsoft 365 ou Excel 2021 e posteriores), a função FILTER oferece uma forma dinâmica e intuitiva de extrair todos os valores que cumprem múltiplos critérios. Esta solução é altamente recomendada para quem precisa que os resultados sejam atualizados automaticamente à medida que os dados ou critérios mudam — sem exigir a introdução complexa de fórmulas matriciais.

1. Numa célula vazia, introduza uma fórmula semelhante a esta:

=FILTER(F3:F22, (B3:B22=J3)*(C3:C22=J4))

Nesta fórmula:

• F3:F22 é a sua coluna Montante.
• B3:B22 é a coluna Data, comparada com a data em J3.
• C3:C22 é a coluna Fruta, comparada com a fruta em J4.

Se pretender adicionar uma terceira condição, como corresponder à coluna Peso (E3:E22)a um valor em J5, expanda a fórmula:

=FILTER(F3:F22, (B3:B22=J3)*(C3:C22=J4)*(E3:E22=J5))

Após premir Enter, o Excel apresentará todos os montantes que satisfazem todos os critérios. Se não for encontrada nenhuma correspondência, a fórmula devolverá o erro #CALC!, que pode tratar com IFERROR:

=IFERROR(FILTER(F3:F22, (B3:B22=J3)*(C3:C22=J4)*(E3:E22=J5)), "No match")

Vantagens:

• Os resultados atualizam-se automaticamente sempre que os seus dados ou critérios forem alterados.
• As fórmulas são mais fáceis de manter e expandir do que as antigas fórmulas de matriz.
• Devolve todas as correspondências, em vez de apenas o primeiro valor encontrado.

Limitação: Apenas disponível no Microsoft 365, Excel 2021 ou versões posteriores. Não suportado em versões anteriores.