PROCV com Múltiplos Critérios

O PROCV faz uma busca numa lista para retornar uma correspondência, mas está limitado a apenas um critério na busca. Esta página mostra como contornar essa situação.

Introdução

Observe tabela abaixo:

Com base na tabela vermelha, desejo saber qual é o telefone Comercial de Felipe. Podemos tentar usar o PROCV e escrever em I5:

=PROCV(G5;B5:D19;3;0)

O resultado será 3244-1000, que não é o que desejamos. O PROCV retorna a primeira correspondência encontrada e, neste caso, não considerou o critério Comercial. A seguir, algumas técnicas para contornar essa limitação.

Criar Coluna Temporária

Uma forma usando o PROCV para resolver esse problema é concatenar as colunas de critério à esquerda da tabela de busca e concatenar também os critérios do valor procurado. Veja uma ilustração dessa técnica:

A fórmula em A5 é:

=B5&"|"&C5

Copie essa fórmula e cole até a célula A19.

A fórmula em F5 é:

=G5&"|"&H5

Podemos usar agora o PROCV normalmente. Escreva em B16:

=PROCV(F5;A5:D19;4;0)

Alternativamente, poderíamos dispensar a célula auxiliar F5 e embedar diretamente os critérios na fórmula, ficando assim:

=PROCV(G5&"|"&H5;A5:D19;4;0)

Nem sempre você poderá usar a solução de concatenação. Por exemplo, se essa planilha estiver protegida, você não conseguirá povoar a coluna auxiliar. Além disso, há a questão de estragar a estética da planilha. A seguir, uma solução melhor.

Arranjo com ÍNDICE e CORRESP

É recomendável ler a página de fórmulas de arranjo antes de prosseguir.

As fórmulas ÍNDICE e CORRESP estão bem detalhadas aqui no site. Se não as conhece, recomendo que as estude. Abaixo segue mais uma imagem da planilha com cores para facilitar o entendimento da fórmula apresentada:

A fórmula em I5 é:

=ÍNDICE(D5:D19;CORRESP(1;(B5:B19=G5)*(C5:C19=H5);0))

Trata-se de uma fórmula de arranjo – entre-a com Ctrl+Shift+Enter e não somente com Enter.

Explicação

O que desejamos retornar é o Número, e ele aparece logo no início da fórmula. As colunas Funcionário e Tipo compõe o critério que será comparado aos valores em roxo.

Para entender a fórmulas, primeiro vamos analisar a expressão:

(B5:B19=G5)*(C5:C19=H5) 

Sua representação, avaliação e resultado são representados abaixo:

Note que ao multiplicar valores booleanos, o Excel considera que FALSO possui valor 0 e VERDADEIRO possui valor 1.

A expressão reduzida vai para:

CORRESP(1;{0;0;0;0;0;0;0;0;1;0;0;0;0;0;0};0)

Que resulta em 9.

Logo, a fórmula se reduz à função ÍNDICE em:

=ÍNDICE(D5:D19;9)

E o resultado dessa fórmula é 3947-5647, que é o resultado esperado.

Download

Para fazer download da pasta de trabalho usada neste artigo, clique aqui.

Sobre Felipe Gualberto

Microsoft Most Valuable Professional (MVP) de Excel.
Esta entrada foi publicada em Tutoriais e marcada com a tag , , , , , . Adicione o link permanente aos seus favoritos.