Função VLOOKUP no Excel – Guia para Iniciantes: Sintaxe e Exemplos

Conteúdo

Função PROCV no Excel – descrição geral e sintaxe
- Sintaxe da função PROCV
Exemplos de PROCV
Correspondência exata ou aproximada na função PROCV
- Exemplo 1: encontrar uma correspondência exata com PROCV
- Exemplo 2: usando PROCV para encontrar uma correspondência aproximada
PROCV no Excel – você precisa se lembrar disso!

Hoje estamos iniciando uma série de artigos que descrevem um dos recursos mais úteis do Excel − VPR (PROCV). Essa função, ao mesmo tempo, é uma das mais complexas e menos compreendidas.

Neste tutorial sobre VPR Tentarei apresentar o básico da forma mais simples possível para tornar o processo de aprendizado o mais claro possível para usuários inexperientes. Além disso, estudaremos vários exemplos com fórmulas do Excel que demonstrarão os casos de uso mais comuns para a função VPR.

Função PROCV no Excel – descrição geral e sintaxe

Então o que é VPR? Bem, em primeiro lugar, é uma função do Excel. O que ela faz? Ele procura o valor especificado e retorna o valor correspondente da outra coluna. Tecnicamente falando, VPR procura o valor na primeira coluna do intervalo fornecido e retorna o resultado de outra coluna na mesma linha.

Na aplicação mais comum, a função VPR pesquisa o banco de dados por um determinado identificador exclusivo e extrai algumas informações relacionadas a ele do banco de dados.

Primeira letra do nome da função VPR (PROCV) significa Вverticais (Vvertical). Por ele você pode distinguir VPR da GPR (HLOOKUP), que procura um valor na linha superior de um intervalo − Гhorizontais (Hhorizontalmente).

função VPR disponível no Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP e Excel 2000.

Sintaxe da função PROCV

função VPR (VLOOKUP) tem a seguinte sintaxe:

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])

Como você pode ver, uma função VPR no Microsoft Excel tem 4 opções (ou argumentos). Os três primeiros são obrigatórios, o último é opcional.

lookup_value (lookup_value) – O valor a ser procurado. Pode ser um valor (número, data, texto) ou uma referência de célula (contendo o valor de pesquisa) ou um valor retornado por alguma outra função do Excel. Por exemplo, esta fórmula procurará o valor 40:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

Se o valor de pesquisa for menor que o menor valor na primeira coluna do intervalo que está sendo pesquisado, a função VPR reportará um erro #NO (#N / D).

table_array (tabela) – duas ou mais colunas de dados. Lembre-se, a função VPR sempre procura o valor na primeira coluna do intervalo dado no argumento table_array (tabela). O intervalo visível pode conter vários dados, como texto, datas, números, booleanos. A função não diferencia maiúsculas de minúsculas, o que significa que caracteres maiúsculos e minúsculos são considerados iguais. Portanto, nossa fórmula procurará o valor 40 em células de A2 para A15, porque A é a primeira coluna do intervalo A2:B15 fornecido no argumento table_array (tabela):
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

col_index_num (column_number) é o número da coluna no intervalo fornecido a partir do qual o valor na linha encontrada será retornado. A coluna mais à esquerda no intervalo fornecido é 1, a segunda coluna é 2, a terceira coluna é 3 e assim por diante. Agora você pode ler a fórmula inteira:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)
Fórmula em busca de valor 40 no intervalo A2: A15 e retorna o valor correspondente da coluna B (porque B é a segunda coluna no intervalo A2:B15).

Se o valor do argumento col_index_num (column_number) menor que 1então VPR reportará um erro #VALOR! (#VALOR!). E se for mais do que o número de colunas no intervalo table_array (tabela), a função retornará um erro # Ref! (#LINK!).

Pesquisa de alcance (range_lookup) – determina o que procurar:
- correspondência exata, o argumento deve ser igual FALSE (FALSO);
- correspondência aproximada, argumento é igual CÓDIGO VERDADEIRO (VERDADEIRO) ou não especificado.
Este parâmetro é opcional, mas muito importante. Mais adiante neste tutorial sobre VPR Mostrarei alguns exemplos explicando como escrever fórmulas para encontrar correspondências exatas e aproximadas.

Exemplos de PROCV

espero que a função VPR ficar um pouco mais claro para você. Agora vamos ver alguns casos de uso VPR em fórmulas com dados reais.

Como usar o PROCV para pesquisar em outra planilha do Excel

Na prática, fórmulas com função VPR raramente são usados para pesquisar dados na mesma planilha. Na maioria das vezes, você estará procurando e recuperando valores correspondentes de outra planilha.

Para utilizar VPR, pesquise em outra planilha do Microsoft Excel, você deve no argumento table_array (tabela) especifique o nome da planilha com um ponto de exclamação seguido por um intervalo de células. Por exemplo, a fórmula a seguir mostra que o intervalo A2: B15 está em uma folha chamada Sheet2.

=VLOOKUP(40,Sheet2!A2:B15,2)

=ВПР(40;Sheet2!A2:B15;2)

Obviamente, o nome da planilha não precisa ser inserido manualmente. Basta começar a digitar a fórmula, e quando se trata do argumento table_array (tabela), mude para a planilha desejada e selecione o intervalo de células desejado com o mouse.

A fórmula mostrada na captura de tela abaixo procura o texto “Produto 1” na coluna A (é a 1ª coluna do intervalo A2:B9) em uma planilha Valores.

=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)

=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)

Lembre-se de que ao pesquisar um valor de texto, você deve colocá-lo entre aspas (“”), como geralmente é feito em fórmulas do Excel.

Para argumento table_array (tabela) é desejável sempre usar referências absolutas (com o sinal $). Nesse caso, o intervalo de pesquisa permanecerá inalterado ao copiar a fórmula para outras células.

Pesquisar em outra pasta de trabalho com PROCV

Funcionar VPR trabalhou entre duas pastas de trabalho do Excel, você precisa especificar o nome da pasta de trabalho entre colchetes antes do nome da planilha.

Por exemplo, abaixo está uma fórmula que procura o valor 40 na folha Sheet2 no livro Números.xlsx:

=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)

=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)

Aqui está a maneira mais fácil de criar uma fórmula no Excel com VPRque vincula a outra pasta de trabalho:

Abra os dois livros. Isso não é obrigatório, mas é mais fácil criar uma fórmula dessa maneira. Você não deseja inserir o nome da pasta de trabalho manualmente, não é? Além disso, ele irá protegê-lo de erros de digitação acidentais.
Comece a digitar uma função VPRe quando se trata do argumento table_array (tabela), mude para outra pasta de trabalho e selecione o intervalo de pesquisa necessário nela.

A captura de tela abaixo mostra a fórmula com a pesquisa definida como um intervalo na pasta de trabalho Lista de preços.xlsx na folha Valores.

função VPR funcionará mesmo quando você fechar a pasta de trabalho pesquisada e o caminho completo para o arquivo da pasta de trabalho aparecer na barra de fórmulas, conforme mostrado abaixo:

Se o nome da pasta de trabalho ou planilha contiver espaços, ele deverá ser colocado entre apóstrofos:

=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)

=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)

Como usar um intervalo ou tabela nomeado em fórmulas com PROCV

Se você planeja usar o mesmo intervalo de pesquisa em várias funções VPR, você pode criar um intervalo nomeado e inserir seu nome na fórmula como um argumento table_array (tabela).

Para criar um intervalo nomeado, basta selecionar as células e inserir um nome apropriado no campo Nome, à esquerda da barra de fórmulas.

Agora você pode escrever a seguinte fórmula para encontrar o preço de um produto Produto 1:

=VLOOKUP("Product 1",Products,2)

=ВПР("Product 1";Products;2)

A maioria dos nomes de intervalo funciona para toda a pasta de trabalho do Excel, portanto, não há necessidade de especificar o nome da planilha para o argumento table_array (tabela), mesmo que a fórmula e o intervalo de pesquisa estejam em planilhas diferentes. Se eles estiverem em pastas de trabalho diferentes, antes do nome do intervalo, você precisará especificar o nome da pasta de trabalho, por exemplo, assim:

=VLOOKUP("Product 1",PriceList.xlsx!Products,2)

=ВПР("Product 1";PriceList.xlsx!Products;2)

Então a fórmula parece muito mais clara, concorda? Além disso, usar intervalos nomeados é uma boa alternativa para referências absolutas porque o intervalo nomeado não muda quando você copia a fórmula para outras células. Isso significa que você pode ter certeza de que o intervalo de pesquisa na fórmula permanecerá sempre correto.

Se você converter um intervalo de células em uma planilha do Excel completa usando o comando mesa guia (Tabela) Inclusão (Inserir), quando você selecionar um intervalo com o mouse, o Microsoft Excel adicionará automaticamente os nomes das colunas (ou o nome da tabela, se você selecionar a tabela inteira) à fórmula.

A fórmula final será mais ou menos assim:

=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)

=ВПР("Product 1";Table46[[Product]:[Price]];2)

Ou talvez até assim:

=VLOOKUP("Product 1",Table46,2)

=ВПР("Product 1";Table46;2)

Ao usar intervalos nomeados, os links apontarão para as mesmas células, não importa onde você copie a função VPR dentro da pasta de trabalho.

Usando curingas em fórmulas PROCV

Tal como acontece com muitas outras funções, VPR Você pode usar os seguintes caracteres curinga:

Ponto de interrogação (?) – substitui qualquer caractere único.
Asterisco (*) – substitui qualquer sequência de caracteres.

Usando curingas em funções VPR pode ser útil em muitos casos, por exemplo:

Quando você não se lembra exatamente do texto que precisa encontrar.
Quando você deseja encontrar alguma palavra que faz parte do conteúdo de uma célula. Saiba que VPR pesquisa pelo conteúdo da célula como um todo, como se a opção estivesse habilitada Combine todo o conteúdo da célula (Célula inteira) na pesquisa padrão do Excel.
Quando uma célula contém espaços extras no início ou no final do conteúdo. Em tal situação, você pode quebrar a cabeça por um longo tempo, tentando descobrir por que a fórmula não funciona.

Exemplo 1: procurando texto que comece ou termine com determinados caracteres

Digamos que você queira pesquisar um cliente específico no banco de dados mostrado abaixo. Você não se lembra do sobrenome dele, mas sabe que começa com “ack”. Aqui está uma fórmula que fará o trabalho muito bem:

=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)

=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)

Agora que você tem certeza de que encontrou o nome correto, pode usar a mesma fórmula para encontrar o valor pago por esse cliente. Para fazer isso, basta alterar o terceiro argumento da função VPR para o número da coluna desejada. No nosso caso, esta é a coluna C (3ª no intervalo):

=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)

=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)

Aqui estão mais alguns exemplos com curingas:

~ Encontre um nome que termine em “man”:

=VLOOKUP("*man",$A$2:$C$11,1,FALSE)

=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)

~ Encontre um nome que comece com "ad" e termine com "son":

=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)

=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)

~ Encontramos o primeiro nome na lista, composta por 5 caracteres:

=VLOOKUP("?????",$A$2:$C$11,1,FALSE)

=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)

Funcionar VPR com curingas funcionou corretamente, como o quarto argumento você deve sempre usar FALSE (FALSO). Se o intervalo de pesquisa contiver mais de um valor que corresponda aos termos de pesquisa com curingas, o primeiro valor encontrado será retornado.

Exemplo 2: combinar curingas e referências de células em fórmulas PROCV

Agora vamos ver um exemplo um pouco mais complexo de como pesquisar usando a função VPR por valor em uma célula. Imagine que a coluna A é uma lista de chaves de licença e a coluna B é uma lista de nomes que possuem uma licença. Além disso, você tem uma parte (vários caracteres) de algum tipo de chave de licença na célula C1 e deseja encontrar o nome do proprietário.

Isso pode ser feito usando a seguinte fórmula:

=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)

=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)

Essa fórmula procura o valor da célula C1 no intervalo fornecido e retorna o valor correspondente da coluna B. Observe que, no primeiro argumento, usamos um caractere e comercial (&) antes e depois da referência da célula para vincular a string de texto.

Como você pode ver na figura abaixo, a função VPR retorna “Jeremy Hill” porque sua chave de licença contém a sequência de caracteres da célula C1.

Observe que o argumento table_array (tabela) na captura de tela acima contém o nome da tabela (Tabela7) em vez de especificar um intervalo de células. Foi o que fizemos no exemplo anterior.

Correspondência exata ou aproximada na função PROCV

E, finalmente, vamos dar uma olhada no último argumento especificado para a função VPR - Pesquisa de alcance (intervalo_view). Como mencionado no início da lição, este argumento é muito importante. Você pode obter resultados completamente diferentes na mesma fórmula com seu valor CÓDIGO VERDADEIRO (Verdadeiro ou FALSE (FALSO).

Primeiro, vamos descobrir o que o Microsoft Excel significa por correspondências exatas e aproximadas.

Se o argumento Pesquisa de alcance (range_lookup) é igual a FALSE (FALSE), a fórmula procura uma correspondência exata, ou seja, exatamente o mesmo valor fornecido no argumento lookup_value (valor_procurado). Se na primeira coluna do intervalo tcapaz_array (tabela) encontra dois ou mais valores que correspondem ao argumento lookup_value (search_value), então o primeiro será selecionado. Se nenhuma correspondência for encontrada, a função relatará um erro #NO (#N / D). Por exemplo, a fórmula a seguir relatará um erro #NO (#N/A) se não houver valor no intervalo A2:A15 4:
=VLOOKUP(4,A2:B15,2,FALSE)
=ВПР(4;A2:B15;2;ЛОЖЬ)
Se o argumento Pesquisa de alcance (range_lookup) é igual a CÓDIGO VERDADEIRO (VERDADEIRO), a fórmula procura uma correspondência aproximada. Mais precisamente, primeiro a função VPR procura uma correspondência exata e, se nenhuma for encontrada, seleciona uma correspondência aproximada. Uma correspondência aproximada é o maior valor que não excede o valor especificado no argumento. lookup_value (valor_procurado).

Se o argumento Pesquisa de alcance (range_lookup) é igual a CÓDIGO VERDADEIRO (VERDADEIRO) ou não especificado, os valores da primeira coluna do intervalo devem ser classificados em ordem crescente, ou seja, do menor para o maior. Caso contrário, a função VPR pode retornar um resultado errado.

Para entender melhor a importância da escolha CÓDIGO VERDADEIRO (VERDADE) ou FALSE (FALSE), vamos ver mais algumas fórmulas com a função VPR e veja os resultados.

Exemplo 1: encontrar uma correspondência exata com PROCV

Como você se lembra, para procurar uma correspondência exata, o quarto argumento da função VPR deve importar FALSE (FALSO).

Vamos voltar para a tabela do primeiro exemplo e descobrir qual animal pode se mover em uma velocidade 50 milhas por hora. Acredito que esta fórmula não lhe causará nenhuma dificuldade:

=VLOOKUP(50,$A$2:$B$15,2,FALSE)

=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)

Observe que nosso intervalo de pesquisa (coluna A) contém dois valores 50 – nas células A5 и A6. Fórmula retorna valor da célula B5. Por quê? Porque ao procurar uma correspondência exata, a função VPR usa o primeiro valor encontrado que corresponde ao que está sendo procurado.

Exemplo 2: usando PROCV para encontrar uma correspondência aproximada

Quando você usa a função VPR para procurar uma correspondência aproximada, ou seja, quando o argumento Pesquisa de alcance (range_lookup) é igual a CÓDIGO VERDADEIRO (VERDADEIRO) ou omitido, a primeira coisa que você precisa fazer é classificar o intervalo pela primeira coluna em ordem crescente.

Isso é muito importante porque a função VPR retorna o próximo maior valor após o dado e, em seguida, a pesquisa é interrompida. Se você negligenciar a classificação correta, terá resultados muito estranhos ou uma mensagem de erro. #NO (#N / D).

Agora você pode usar uma das seguintes fórmulas:

=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)

=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)

Como você pode ver, eu quero descobrir qual dos animais tem a velocidade mais próxima de 69 milhas por hora. E aqui está o resultado que a função retornou para mim VPR:

Como você pode ver, a fórmula retornou um resultado Antílope (Antílope), cuja velocidade 61 milhas por hora, embora a lista também inclua Cheetah (Cheetah) que corre em velocidade 70 milhas por hora, e 70 está mais perto de 69 do que 61, não é? Por que isso está acontecendo? Porque a função VPR ao pesquisar uma correspondência aproximada, retorna o maior valor que não é maior que o que está sendo pesquisado.

Espero que esses exemplos lancem alguma luz sobre como trabalhar com a função VPR no Excel, e você não a vê mais como uma estranha. Agora não custa repetir brevemente os pontos-chave do material que estudamos para melhor fixá-lo na memória.

PROCV no Excel – você precisa se lembrar disso!

função VPR O Excel não pode olhar para a esquerda. Ele sempre procura o valor na coluna mais à esquerda do intervalo dado pelo argumento table_array (tabela).
Em função VPR todos os valores não diferenciam maiúsculas de minúsculas, ou seja, letras pequenas e grandes são equivalentes.
Se o valor que você está procurando for menor que o valor mínimo na primeira coluna do intervalo pesquisado, a função VPR reportará um erro #NO (#N / D).
Se 3º argumento col_index_num (column_number) menor que 1função VPR reportará um erro #VALOR! (#VALOR!). Se for maior que o número de colunas no intervalo table_array (tabela), a função reportará um erro # Ref! (#LINK!).
Use referências de célula absolutas no argumento table_array (tabela) para que o intervalo de pesquisa correto seja preservado ao copiar a fórmula. Tente usar intervalos nomeados ou tabelas no Excel como alternativa.
Ao fazer uma pesquisa de correspondência aproximada, lembre-se de que a primeira coluna do intervalo que você está procurando deve ser classificada em ordem crescente.
Finalmente, lembre-se da importância do quarto argumento. Usar valores CÓDIGO VERDADEIRO (VERDADE) ou FALSE (FALSO) deliberadamente e você se livrará de muitas dores de cabeça.

Nos seguintes artigos do nosso tutorial de funções VPR no Excel, aprenderemos exemplos mais avançados, como realizar vários cálculos usando VPR, extraindo valores de várias colunas e muito mais. Obrigado por ler este tutorial e espero vê-lo novamente na próxima semana!

Função PROCV no Excel – Guia para iniciantes: sintaxe e exemplos