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 vários critérios
Suponha que você tenha dados com o nome do aluno, o tipo de exame e a pontuação de matemática (conforme mostrado abaixo):
Usar a função PROCV 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 vários critérios - usando uma coluna auxiliar
Eu sou um fã de colunas auxiliares no Excel.
Encontro duas vantagens significativas no uso de colunas auxiliares em vez de fórmulas de matriz:
- Isso torna mais fácil entender o que está acontecendo na planilha.
- Isso o 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 Unit Test ou Matt e Mid-Term.
Aqui estão as etapas:
- Insira uma coluna auxiliar entre as colunas B e C.
- Use a seguinte fórmula na coluna auxiliar: = A2 & ”|” & B2
- Isso criaria qualificadores exclusivos para cada instância, conforme mostrado abaixo.
- Isso criaria qualificadores exclusivos para cada instância, conforme mostrado abaixo.
- Use a seguinte fórmula em G3 = PROCV ($ F3 & ”|” & G $ 2, $ C $ 2: $ D $ 19,2,0)
- Copie para todas as células.
Como é que 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 sejam 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, esse valor de pesquisa é usado para obter a pontuação de C2: D19.
Esclarecimentos:
Há algumas perguntas que provavelmente vêm à sua mente, então pensei em tentar respondê-las 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 pela 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 as mesmas. Mas se você usar um separador, mesmo a combinação seria diferente (D2 e D3).
- Por que 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 na extrema esquerda. 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 isso sou só eu.
Agora não existe um tamanho único que sirva para todos. Algumas pessoas podem preferir não usar nenhuma coluna auxiliar ao usar PROCV com vários critérios.
Portanto, aqui está o método de coluna não auxiliar para você.
Baixe o arquivo de exemplo
PROCV com múltiplos critérios - usando a função CHOOSE
Usar fórmulas de matriz em vez de colunas auxiliares economiza espaço na 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 lhe dará 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 é que isso funciona?
A fórmula também usa o conceito de 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 do auxiliar virtual.
Na ilustração acima, conforme eu seleciono a parte ESCOLHER da fórmula e pressiono F9, ela mostra o resultado que a fórmula ESCOLHER daria.
O resultado é {“Matt | Teste de unidade”, 91; ”Bob | Teste de unidade”, 52;…}
É uma matriz em que 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 PROCV, 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).
Existe alguma outra maneira que você conhece de fazer isso? Se sim, compartilhe comigo na seção de comentários.
Você também pode gostar dos seguintes tutoriais do LOOKUP:
- VLOOKUP vs. ÍNDICE / CORRESPONDÊNCIA
- Obtenha vários valores de pesquisa sem repetição em uma única célula.
- Como fazer PROCV com distinção entre maiúsculas e minúsculas.
- Use IFERROR com VLOOKUP para se livrar de erros # N / A.
