VLOOKUP em duas ou mais colunas de critérios – Excel University

Se você já tentou usar uma função VLOOKUP com duas ou mais colunas de critérios, descobriu rapidamente que simplesmente não foi construído para esse propósito. Felizmente, há outra função que pode funcionar como alternativa a VLOOKUP dependendo do que você deseja retornar.

Objetivo de pesquisa de várias colunas

Primeiro, vamos confirmar nosso objetivo olhando para um exemplo de pasta de trabalho. Exportamos algumas informações do nosso sistema de contabilidade e basicamente resumem os totais das transações do mês por classe e por conta. Um exemplo da exportação é mostrado abaixo:

A partir desses dados exportados, gostaríamos de recuperar os valores selecionados com base no colunas de classe e conta. Queremos recuperar os valores e colocá-los em nosso pequeno relatório, ilustrado abaixo:

Se você estiver familiarizado com a PROCV , parece natural tentar construir o relatório com esta função porque, afinal, esta é uma tarefa de pesquisa. E as tarefas de pesquisa são mais bem resolvidas com funções de pesquisa tradicionais … certo? Bem, isto depende. Depende do que você está tentando recuperar.

Soma condicional para pesquisas

Se você está tentando recuperar um valor numérico, como um valor, uma função de pesquisa tradicional pode não seja sua melhor aposta. Aqui está o porquê. A partir do Excel 2007, a Microsoft incluiu a função de soma condicional SUMIFS. Essa função de soma de múltiplas condições foi projetada para somar uma coluna de números e incluir apenas linhas que atendam a uma ou mais condições. Os pontos ainda estão começando a se conectar?

Se aplicarmos essa ideia à nossa tarefa, perceberemos rapidamente que poderíamos usar essa função de soma condicional para recuperar os valores do nosso relatório.

O primeiro argumento da função SUMIFS é o intervalo de soma, ou seja, a coluna de números a somar. No nosso caso, a coluna que possui o valor que desejamos retornar. Os argumentos restantes vêm em pares: o intervalo de critérios e o valor dos critérios.

É útil pensar sobre a função nestes termos: some esta coluna (argumento 1), inclua apenas as linhas onde esta coluna (argumento 2) é igual a este valor (argumento 3), e onde esta coluna (argumento 4) é igual a este valor (argumento 5), e onde … e assim por diante, até 127 pares.

Assim, para preencher nosso relatório, recuperaremos os valores de valor da exportação e combinaremos as colunas de classe e conta, conforme mostrado abaixo.

Se acontecer de haver várias linhas com a mesma classe e contas, a função SUMIFS retornará a soma de todos os itens correspondentes.

Como você pode ver, se o valor você estão tentando retornar é um número, então a função SUMIFS simplifica a execução de pesquisas em várias colunas. Mas, e se o valor que você está tentando retornar não for um número? Bem, então você precisará usar uma função de pesquisa tradicional, conforme discutido abaixo.

Usando VLOOKUP com o método SUMIFS

Um método é usar VLOOKUP e SUMIFS em uma única fórmula. Essencialmente, você usa SUMIFS como o primeiro argumento de VLOOKUP. Este método é totalmente explorado nesta postagem da Excel University:

Usando VLOOKUP com o método CONCATENATE

Se você está tentando retornar uma string de texto em vez de um número, ou está usando uma versão de Excel que não tem SOMASE, então provavelmente você está preso ao uso de uma função de pesquisa tradicional, como VLOOKUP, juntamente com a função CONCATENAR para gerar uma única coluna de pesquisa exclusiva. Essa abordagem é bastante bem documentada, mas a ideia básica é assim: crie uma única coluna de pesquisa primeiro e, em seguida, use VLOOKUP.

Nosso exemplo será uma lista de funcionários, conforme ilustrado abaixo:

Precisamos recuperar o estado da lista de funcionários para nosso pequeno relatório mostrado abaixo:

Como o valor que estamos tentando retornar, o estado, é uma string de texto e não um número, somos impedidos de usar a função SUMIFS. Portanto, vamos precisar ir à velha escola com VLOOKUP e CONCATENATE.

Começamos construindo uma coluna auxiliar que basicamente cria os valores de pesquisa combinados. Isso pode ser feito facilmente com a função CONCATENATE ou o operador de concatenação (&). Esta nova coluna de pesquisa é ilustrada na coluna B abaixo:

Agora temos uma única coluna de pesquisa que pode ser usada com um função de pesquisa tradicional, como VLOOKUP. O relatório pode ser preenchido procurando os nomes combinados dentro do novo intervalo de pesquisa, conforme mostrado abaixo:

Esta mesma abordagem pode ser usado quando duas, três ou mais colunas de pesquisa precisam ser consideradas.

Conclusão

Além de ser capaz de realizar pesquisas de várias colunas quando o valor de retorno é numérico, a função SUMIFS tem benefícios adicionais quando comparada às funções de pesquisa tradicionais. Por exemplo, ele retorna zero quando nenhum valor correspondente é encontrado, retorna a soma de todas as correspondências, oferece suporte a operadores de comparação e não será interrompido quando uma nova coluna for inserida entre as colunas de pesquisa e de retorno.

Portanto, quando você estiver prestes a interromper a função VLOOKUP para fazer uma tarefa de pesquisa, considere usar SOMASE. Acredite ou não, a função SUMIFS é uma ótima função de pesquisa.

Se você tiver alguma outra abordagem preferencial para pesquisas de várias colunas, adoraríamos saber mais … por favor, poste um comentário abaixo.

Arquivo de amostra

Se você quiser brincar com a pasta de trabalho usada para gerar as capturas de tela acima, fique à vontade para baixar o arquivo de amostra:

MultColumnLookup