Assistir ao vídeo – Como usar a função VLOOKUP com vários critérios

A função VLOOKUP do Excel, em sua forma básica, pode procurar um valor de pesquisa e retornar o valor correspondente da linha especificada.

Mas frequentemente é necessário usar a VLOOKUP do Excel com vários critérios.

Como usar VLOOKUP com múltiplos critérios

Suponha que você tenha dados com o nome do aluno, tipo de exame e pontuação de matemática (conforme mostrado abaixo):

Usar a função VLOOKUP para obter a pontuação de matemática de cada aluno para os respectivos níveis de exame pode ser um desafio.

Pode-se argumentar que a melhor opção seria reestruturar o conjunto de dados ou usar uma Tabela Dinâmica. Se isso funcionar para você, nada disso. Mas, em muitos casos, você fica preso aos dados que possui e a tabela dinâmica pode não ser uma opção.

Nesses casos, este tutorial é para você.

Agora, há duas maneiras de obter o valor de pesquisa usando VLOOKUP com vários critérios.

• Usando uma coluna auxiliar.
• Usando a função CHOOSE.

PROCV com múltiplos critérios – usando uma coluna auxiliar

Eu sou um fã de colunas auxiliares no Excel.

Eu encontro duas vantagens significativas no uso de colunas auxiliares em vez de fórmulas de matriz:

• Torna mais fácil entender o que está acontecendo na planilha.
• Torna mais rápido em comparação com as funções de matriz (perceptível em grandes conjuntos de dados).

Agora, não me entenda mal. Não sou contra fórmulas de matriz. Eu amo as coisas incríveis que podem ser feitas com fórmulas de matriz. É que eu os guardo para ocasiões especiais quando todas as outras opções não ajudam.

Voltando à questão em questão, a coluna auxiliar é necessária para criar um qualificador exclusivo. Esse qualificador exclusivo pode então ser usado para pesquisar o valor correto. Por exemplo, há três Matt nos dados, mas há apenas uma combinação de Matt e Teste de Unidade ou Matt e Mid-Term.

Aqui estão as etapas:

Como isso funciona?

Criamos qualificadores exclusivos para cada instância de um nome e do exame. Na função VLOOKUP usada aqui, o valor de pesquisa foi modificado para $ F3 & ”|” & G $ 2 para que ambos os critérios de pesquisa são combinados e usados como um único valor de pesquisa. Por exemplo, o valor de pesquisa para a função VLOOKUP em G2 é Matt | Unit Test. Agora, este valor de pesquisa é usado para obter a pontuação de C2: D19.

Esclarecimentos:

Há algumas perguntas que provavelmente virão à sua mente, então pensei: tentarei respondê-la aqui:

• Por que usei | símbolo ao juntar os dois critérios? – Em algumas condições excepcionalmente raras (mas possíveis), você pode ter dois critérios que são diferentes, mas acabam dando o mesmo resultado quando combinados. Aqui está um exemplo muito simples (perdoe-me por minha falta de criatividade aqui):

Observe que enquanto A2 e A3 são diferentes e B2 e B3 são diferentes, as combinações acabam sendo iguais. Mas se você usar um separador, até mesmo a combinação seria diferente (D2 e D3).

• Por que eu inseri a coluna auxiliar entre as colunas B e C e não na extrema esquerda? – Não há mal nenhum em inserir a coluna auxiliar no extremo esquerdo. Na verdade, se você não quiser alterar os dados originais, esse deve ser o caminho a percorrer. Fiz isso porque me faz usar menos número de células na função VLOOKUP. Em vez de ter 4 colunas na matriz da tabela, eu poderia gerenciar com apenas 2 colunas. Mas sou só eu.

Agora não existe um tamanho que sirva para todos. Algumas pessoas podem preferir não usar nenhuma coluna auxiliar ao usar VLOOKUP com vários critérios.

Então, aqui está o método de coluna não auxiliar para você.

Baixe o arquivo de exemplo

VLOOKUP com vários critérios – usando a função CHOOSE

Usando fórmulas de matriz em vez de auxiliar colunas economizam o espaço da planilha e o desempenho pode ser igualmente bom se usado menos vezes em uma pasta de trabalho.

Considerando o mesmo conjunto de dados usado acima, aqui está a fórmula que fornecerá o resultado :

= VLOOKUP ($ E3 & ”|” & F $ 2, ESCOLHER ({1,2} , $ A $ 2: $ A $ 19 & ”|” & $ B $ 2: $ B $ 19, $ C $ 2: $ C $ 19 ), 2,0)

Como esta é uma fórmula de matriz, use-a com Control + Shift + Enter, em vez de apenas Enter.

Como isso funciona?

A fórmula também usa o conceito de uma coluna auxiliar. A diferença é que, em vez de colocar a coluna auxiliar na planilha, considere-a como dados auxiliares virtuais que fazem parte da fórmula.

Deixe-me mostrar o que quero dizer com dados auxiliares virtuais.

Na ilustração acima, conforme seleciono a parte ESCOLHER da fórmula e pressiono F9, ele mostra o resultado que o CHOOSE formula daria.

O resultado é {“Matt | Unit Test”, 91; ”Bob | Unit Test”, 52; ……}

É uma matriz onde uma vírgula representa a próxima célula na mesma linha e o ponto-e-vírgula representa que os dados a seguir estão na próxima coluna. Portanto, esta fórmula cria 2 colunas de dados – uma coluna tem o identificador exclusivo e a outra tem a pontuação.

Agora , quando você usa a função VLOOKUP, ela simplesmente procura o valor na primeira coluna (desses dados de 2 colunas virtuais) e retorna a pontuação correspondente.

Baixe o Arquivo de exemplo

Você também pode usar outras fórmulas para fazer uma pesquisa com vários critérios (como INDEX / MATCH ou SUMPRODUCT).