Ir para o conteúdo principal

Mais de 20 exemplos de VLOOKUP para usuários iniciantes e avançados do Excel

A função PROCV é uma das funções mais populares do Excel. Este tutorial apresentará como usar a função VLOOKUP no Excel com dezenas de exemplos básicos e avançados passo a passo.


Introdução da função VLOOKUP - Sintaxe e Argumentos

No Excel, a função VLOOKUP é uma função poderosa para a maioria dos usuários do Excel, permite que você procure um valor na extremidade esquerda do intervalo de dados e retorne um valor correspondente na mesma linha de uma coluna que você especificou conforme a captura de tela mostrada a seguir .

A sintaxe da função PROCV:

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

Argumentos:

Valor_procurado (obrigatório): O valor que você deseja pesquisar. Pode ser um valor (número, data ou texto) ou referência de célula. Deve estar na primeira coluna do intervalo table_array. 

Tabela_array (obrigatório): O intervalo de dados ou tabela onde a coluna de valor de pesquisa e a coluna de valor de resultado estão localizadas.

Col_index_num (obrigatório): O número da coluna que contém os valores de retorno. Ele começa com 1 da coluna mais à esquerda na matriz da tabela.

Pesquisa de alcance (opcional): um valor lógico que determina se esta função VLOOKUP retornará uma correspondência exata ou aproximada.

  • Correspondência aproximada – 1 / VERDADEIRO / omitido (padrão): Se uma correspondência exata não for encontrada, a fórmula procurará a correspondência mais próxima - o maior valor menor que o valor de pesquisa.
    Perceber: Nesse caso, você deve classificar a coluna de pesquisa (coluna mais à esquerda do intervalo de dados) em ordem crescente, caso contrário, retornará um resultado errado ou um erro #N/A.
  • Correspondência exata – 0 / FALSO: É usado para pesquisar um valor exatamente igual ao valor de pesquisa. Se uma correspondência exata não for encontrada, o valor de erro # N / A será retornado.

Notas de função:

  • A função Vlookup procura apenas um valor da esquerda para a direita.
  • A função Vlookup executa uma pesquisa que não diferencia maiúsculas de minúsculas.
  • Se houver vários valores correspondentes com base no valor de pesquisa, apenas o primeiro correspondente será retornado usando a função Vlookup.

Exemplos básicos de VLOOKUP

Nesta seção, falaremos sobre algumas fórmulas de Vlookup que você usa com frequência.

2.1 Correspondência exata e correspondência aproximada VLOOKUP

 2.1.1 Faça uma VLOOKUP de correspondência exata

Normalmente, se você estiver procurando por uma correspondência exata com a função VLOOKUP, basta usar FALSE como último argumento.

Por exemplo, para obter as pontuações de matemática correspondentes com base nos números de identificação específicos, faça o seguinte:

Copie e cole a fórmula abaixo em uma célula em branco (aqui, selecione G2) e pressione Entrar chave para obter o resultado:

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

Observação: na fórmula acima, há quatro argumentos:

  • F2 é a célula que contém o valor C1005 que você deseja pesquisar;
  • A2: D7 é a matriz da tabela na qual você está realizando a pesquisa;
  • 3 é o número da coluna da qual seu valor correspondente é retornado; (Assim que a função identificar o ID - C1005, ela irá para a terceira coluna da matriz da tabela e retornará os valores na mesma linha do ID - C1005. )
  • FALSE refere-se à correspondência exata.

Como funciona a fórmula VLOOKUP?

Primeiro, ele procura o ID - C1005 na coluna mais à esquerda da tabela. Ele vai de cima para baixo e encontra o valor na célula A6.

Assim que encontra o valor, vai para a direita na terceira coluna e extrai o valor dela.

Então, você obterá o resultado conforme a captura de tela abaixo mostrada:

Nota: Se o valor de pesquisa não for encontrado na coluna mais à esquerda, ele retornará um erro #N/A.
Recursos mais comuns: Encontre, destaque ou identifique duplicatas   |  Excluir linhas em branco   |  Combine colunas ou células sem perder dados   |   Rodada sem Fórmula ...
Super pesquisa: VLookup de múltiplos critérios  |   VLookup de múltiplos valores  |   VLookup em várias planilhas   |   Pesquisa Difusa ...
Lista suspensa avançada: Crie rapidamente uma lista suspensa   |  Lista suspensa de dependentes   |  Lista suspensa de seleção múltipla ...
Gerenciador de colunas: Adicione um número específico de colunas  |  Mover colunas   |  Exibir colunas  |  Compare intervalos e colunas ...
Recursos em destaque: Foco da Grade   |  Vista de Design   |   Grande Barra de Fórmula   |  Gerenciador de pastas de trabalho e planilhas  |  Biblioteca   |  Data Picker  |  Combinar planilhas   |  Criptografar/Descriptografar Células    Enviar e-mails por lista   |  Super Filtro   |   Filtro Especial (em negrito/itálico...) ...
15 principais conjuntos de ferramentas12 Texto Ferramentas (Adicionar texto, Remover Personagens, ...)   |   50+ de cores Tipos (Gráfico de Gantt, ...)   |   Mais de 40 práticos Fórmulas (Calcule a idade com base no aniversário, ...)   |   19 Inclusão Ferramentas (Insira o código QR, Inserir imagem do caminho, ...)   |   12 Conversão Ferramentas (Números para Palavras, Conversão de moedas, ...)   |   7 Unir e dividir Ferramentas (Combinar linhas avançadas, Dividir células, ...)   |   Muito mais...

Kutools para Excel possui mais de 300 recursos, Garantindo que o que você precisa está a apenas um clique de distância...

 
 2.1.2 Faça uma correspondência aproximada VLOOKUP

A correspondência aproximada é útil para pesquisar valores entre intervalos de dados. Se a correspondência exata não for encontrada, o VLOOKUP aproximado retornará o maior valor menor que o valor de pesquisa.

Por exemplo, se você tiver o seguinte intervalo de dados e os pedidos especificados não estiverem na coluna Pedidos, como obter o Desconto mais próximo na coluna B?

Etapa 1: aplique a fórmula VLOOKUP e preencha-a em outras células

Copie e cole a seguinte fórmula em uma célula onde deseja colocar o resultado e arraste a alça de preenchimento para baixo para aplicar esta fórmula a outras células.

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

Resultado:

Agora, você obterá as correspondências aproximadas com base nos valores fornecidos, veja a captura de tela:

notas:

  • Na fórmula acima:
    • D2 é o valor que você deseja que retorne suas informações relativas;
    • A2: B9 é o intervalo de dados;
    • 2 indica o número da coluna que seu valor correspondente é retornado;
    • VERDADEIRO refere-se à correspondência aproximada.
  • A correspondência aproximada retornará o maior valor menor que o valor de pesquisa específico se nenhuma correspondência exata for encontrada.
  • Para usar a função VLOOKUP para obter um valor de correspondência aproximado, você deve classificar a coluna mais à esquerda do intervalo de dados em ordem crescente, caso contrário, retornará um resultado errado.

2.2 Faça um VLOOKUP com distinção entre maiúsculas e minúsculas no Excel

Por padrão, a função VLOOKUP executa uma pesquisa que não diferencia maiúsculas de minúsculas, o que significa que ela trata caracteres maiúsculos e minúsculos como idênticos. Em algum momento, você pode precisar realizar uma pesquisa com distinção entre maiúsculas e minúsculas no Excel, a função VLOOKUP normal pode não resolvê-lo. Nesse caso, você pode usar funções alternativas como ÍNDICE e CORRESP com a função EXATO ou as funções PROC e EXATO.

Por exemplo, eu tenho o seguinte intervalo de dados cuja coluna de ID contém uma string de texto com maiúsculas ou minúsculas, agora, quero retornar a pontuação matemática correspondente do número de ID fornecido.

Etapa 1: aplicar qualquer fórmula e preenchê-la em outras células

Copie e cole qualquer uma das fórmulas abaixo em uma célula em branco onde deseja obter o resultado. Em seguida, selecione a célula da fórmula, arraste a alça de preenchimento até as células onde deseja preencher esta fórmula.

Fórmula 1: Após colar a fórmula, pressione Ctrl + Shift + Enter chaves.

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

Fórmula 2: Após colar a fórmula, pressione Entrar chave.

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

Resultado:

Então você obterá os resultados corretos de que precisa. Veja a captura de tela:

notas:

  • Na fórmula acima:
    • A2: A10 é a coluna que contém os valores específicos que você deseja pesquisar;
    • F2 é o valor de pesquisa;
    • C2: C10 é a coluna de onde o resultado será retornado.
  • Se forem encontradas várias correspondências, esta fórmula sempre retornará a última correspondência.

2.3 Valores VLOOKUP da direita para a esquerda no Excel

A função VLOOKUP sempre pesquisa um valor na coluna mais à esquerda de um intervalo de dados e retorna o valor correspondente de uma coluna à direita. Se você deseja executar um VLOOKUP reverso, o que significa procurar um valor específico na coluna da direita e retornar seu valor correspondente na coluna da esquerda, conforme a captura de tela mostrada abaixo:

Clique para saber os detalhes passo a passo sobre esta tarefa ...


2.4 VLOOKUP o segundo, enésimo ou último valor correspondente no Excel

Normalmente, se vários valores correspondentes forem encontrados ao usar a função Vlookup, apenas o primeiro registro correspondente será retornado. Nesta seção, falarei sobre como obter o segundo, enésimo ou último valor correspondente em um intervalo de dados.

 2.4.1 VLOOKUP e retornar o segundo ou enésimo valor correspondente

Suponha que você tenha uma lista de nomes na coluna A, o curso de treinamento que eles compraram na coluna B. Agora, você está procurando o segundo ou o enésimo curso de treinamento comprado pelo cliente em questão. Veja a captura de tela:

Aqui, a função VLOOKUP pode não resolver esta tarefa diretamente. Mas você pode usar a função INDEX como alternativa.

Etapa 1: aplicar e preencher a fórmula em outras células

Por exemplo, para obter o segundo valor correspondente com base nos critérios fornecidos, aplique a seguinte fórmula em uma célula em branco e pressione Ctrl + Shift + Enter teclas juntas para obter o primeiro resultado. Em seguida, selecione a célula da fórmula, arraste a alça de preenchimento para baixo até as células onde deseja preencher esta fórmula.

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

Resultado:

Agora, todos os segundos valores correspondentes com base nos nomes fornecidos foram exibidos de uma só vez.

Nota: Na fórmula acima:

  • A2: A14 é o intervalo com todos os valores para pesquisa;
  • B2: B14 é o intervalo dos valores correspondentes dos quais você deseja retornar;
  • E2 é o valor de pesquisa;
  • 2 indica o segundo valor correspondente que você deseja obter, para retornar o terceiro valor correspondente, basta alterá-lo para 3.
 2.4.2 VLOOKUP e retornar o último valor correspondente

Se você deseja visualizar e retornar o último valor correspondente conforme a captura de tela mostrada abaixo, VLOOKUP e retornar o último valor correspondente tutorial pode ajudá-lo a obter o último valor correspondente em detalhes.


2.5 VLOOKUP combinando valores entre dois valores ou datas fornecidos

Às vezes, você pode querer pesquisar valores entre dois valores ou datas e retornar os resultados correspondentes, conforme mostrado na captura de tela abaixo. Nesse caso, você pode usar a função PROCV em vez da função VLOOKUP com uma tabela classificada.

 2.5.1 VLOOKUP combinando valores entre dois valores ou datas fornecidos com a fórmula

Etapa 1: organize os dados e aplique a seguinte fórmula

Sua tabela original deve ser um intervalo de dados classificados. Em seguida, copie ou insira a seguinte fórmula em uma célula em branco. Em seguida, arraste a alça de preenchimento para preencher esta fórmula para outras células que você precisa.

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

Resultado:

E agora, você obterá todos os registros correspondentes com base no valor fornecido, veja a captura de tela:

notas:

  • Na fórmula acima:
    • A2: A6 é o intervalo de valores menores;
    • B2: B6 é o intervalo de números maiores;
    • E2 é o valor de pesquisa que você deseja obter seu valor correspondente;
    • C2: C6 é a coluna da qual você deseja retornar um valor correspondente.
  • Esta fórmula também pode ser usada para extrair valores correspondentes entre duas datas, conforme a captura de tela abaixo:
 2.5.2 VLOOKUP combinando valores entre dois valores ou datas fornecidos com um recurso útil

Se você achar difícil lembrar e entender a fórmula acima, aqui apresentarei uma ferramenta fácil – Kutools for Excel, Com o seu LOOKUP entre dois valores recurso, você pode retornar o item correspondente com base no valor ou data específica entre dois valores ou datas com facilidade.

  1. Clique Kutools > Super PROCURA > LOOKUP entre dois valores para ativar esse recurso.
  2. Em seguida, especifique as operações na caixa de diálogo com base em seus dados.
Note: Para aplicar este recurso, você deve baixar Kutools para Excel com teste gratuito de 30 dias primeiramente.


2.6 Usando curingas para correspondências parciais na função VLOOKUP

No Excel, os curingas podem ser usados ​​na função VLOOKUP, que permite realizar uma correspondência parcial em um valor de pesquisa. Por exemplo, você pode usar VLOOKUP para retornar o valor correspondente de uma tabela com base em parte de um valor de pesquisa.

Supondo que eu tenha uma gama de dados como a imagem abaixo mostrada, agora, eu quero extrair a pontuação com base no primeiro nome (não no nome completo). Como resolveria essa tarefa no Excel?

Etapa 1: aplicar e preencher a fórmula em outras células

Copie ou insira a seguinte fórmula em uma célula em branco e, em seguida, arraste a alça de preenchimento para preencher esta fórmula em outras células necessárias:

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

Resultado:

E todas as pontuações correspondentes foram retornadas conforme a captura de tela abaixo:

Nota: Na fórmula acima:

  • E2 & ”*” é o critério para a matemática parcial. Isso significa que você está procurando qualquer valor que comece com o valor na célula E2. (O curinga “*” indica qualquer caractere ou quaisquer caracteres)
  • A2: C11 é o intervalo de dados onde você deseja procurar o valor correspondente;
  • 3 significa retornar o valor correspondente da 3ª coluna do intervalo de dados;
  • Falso indica a matemática exata. (Ao usar curingas, você deve definir o último argumento na função como FALSE ou 0 para habilitar o modo de correspondência exata na função VLOOKUP.)
Tips:
  • Para localizar e retornar os valores correspondentes que terminam com um valor específico, você deve colocar o curinga "*" na frente do valor. Por favor, aplique esta fórmula:
  • =VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)

  • Para pesquisar e retornar o valor correspondente com base em parte da string de texto, seja o texto especificado no início, no final ou no meio da string de texto, basta colocar a referência da célula ou o texto entre dois asteriscos (*) em ambos os lados. Por favor, faça com esta fórmula
  • =VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)


2.7 Valores VLOOKUP de outra planilha

Normalmente, você pode ter que trabalhar com mais de uma planilha, a função VLOOKUP pode ser usada para pesquisar dados de outra planilha como os mesmos de uma planilha.

Por exemplo, você tem duas planilhas conforme a captura de tela mostrada abaixo, para pesquisar e retornar os dados correspondentes da planilha especificada, siga estas etapas:

Etapa 1: aplicar e preencher a fórmula em outras células

Insira ou copie a fórmula abaixo em uma célula em branco onde deseja obter os itens correspondentes. Em seguida, arraste a alça de preenchimento para baixo até as células nas quais deseja aplicar esta fórmula.

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

Resultado:

Você obterá os resultados correspondentes conforme necessário, veja a captura de tela:

Nota: Na fórmula acima:

  • A2 representa o valor de pesquisa;
  • 'Folha de dados'!A2:C15 indica buscar os valores do intervalo A2:C15 na planilha denominada Folha de Dados; (Se o nome da planilha contiver espaço ou caracteres de pontuação, você deve colocar o nome da planilha entre aspas simples, caso contrário, você pode usar diretamente o nome da planilha como =PROCV(A2,Folha de Dados!$A$2:$C$15,3,0) ).
  • 3 é o número da coluna que contém os dados correspondentes dos quais você deseja retornar;
  • 0 significa realizar uma correspondência exata.

2.8 Valores VLOOKUP de outra pasta de trabalho

Esta seção falará sobre pesquisa e retornará os valores correspondentes de uma pasta de trabalho diferente usando a função VLOOKUP.

Por exemplo, digamos que você tenha duas pastas de trabalho. A primeira pasta de trabalho contém uma lista de produtos e seus respectivos custos. Na segunda pasta de trabalho, você deseja extrair o custo correspondente para cada item do produto conforme a captura de tela abaixo.

Passo 1: Aplique e preencha a fórmula

Abra as duas pastas de trabalho que deseja usar e aplique a seguinte fórmula em uma célula onde deseja colocar o resultado na segunda pasta de trabalho. Em seguida, arraste e copie esta fórmula para outras células necessárias

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

Resultado:

notas:

  • Na fórmula acima:
    • B2 representa o valor de pesquisa;
    • '[Lista de produtos.xlsx]Folha1'!A2:B6 indica para pesquisar no intervalo A2:B6 na planilha chamada Sheet1 da lista de produtos da pasta de trabalho; (A referência à pasta de trabalho está entre colchetes e toda a pasta de trabalho + planilha está entre aspas simples.)
    • 2 é o número da coluna que contém os dados correspondentes dos quais você deseja retornar;
    • 0 indica para retornar uma correspondência exata.
  • Se a pasta de trabalho de pesquisa estiver fechada, o caminho completo do arquivo para a pasta de trabalho de pesquisa será mostrado na fórmula conforme a captura de tela a seguir:

2.9 Retornar texto em branco ou específico em vez de 0 ou erro #N/A

Normalmente, quando você usa a função VLOOKUP para retornar um valor correspondente, se a célula correspondente estiver em branco, ela retornará 0. E se o valor correspondente não for encontrado, você obterá um valor de erro de #N/A conforme mostrado na captura de tela abaixo. Se você deseja exibir uma célula em branco ou um valor específico em vez de 0 ou #N/A, VLOOKUP para retornar valor em branco ou específico em vez de 0 ou N/A tutorial pode fazer um favor a você.


Exemplos avançados de VLOOKUP

3.1 Pesquisa bidirecional (VLOOKUP em linha e coluna)

Às vezes, pode ser necessário realizar uma pesquisa bidimensional, o que significa pesquisar um valor na linha e na coluna ao mesmo tempo. Por exemplo, se você tiver o seguinte intervalo de dados e precisar obter o valor de um determinado produto em um trimestre especificado. Esta seção apresentará uma fórmula para lidar com este trabalho no Excel.

No Excel, você pode usar uma combinação das funções VLOOKUP e MATCH para fazer uma pesquisa bidirecional.

Aplique a seguinte fórmula em uma célula em branco e pressione Entrar chave para obter o resultado.

=VLOOKUP(G2, $A$2:$E$7, MATCH(H1, $A$2:$E$2, 0), FALSE)

Nota: Na fórmula acima:

  • G2 é o valor de pesquisa na coluna na qual você deseja obter o valor correspondente com base;
  • A2: E7 é a tabela de dados a partir da qual você procurará;
  • H1 é o valor de pesquisa na linha na qual você deseja obter o valor correspondente com base;
  • A2: E2 são as células dos cabeçalhos das colunas;
  • FALSE indica para obter uma correspondência exata.

3.2 Valor de correspondência VLOOKUP com base em dois ou mais critérios

É fácil procurar o valor correspondente com base em um critério, mas se você tiver dois ou mais critérios, o que poderá fazer?

 3.2.1 Valor de correspondência VLOOKUP com base em dois ou mais critérios com fórmulas

Nesse caso, as funções PROC ou CORRESP e ÍNDICE do Excel podem te ajudar a resolver esse trabalho de forma rápida e fácil.

Por exemplo, eu tenho a tabela de dados abaixo, para retornar o preço combinado com base no produto e tamanho específicos, as fórmulas a seguir podem ajudá-lo.

Etapa 1: aplicar qualquer fórmula

Fórmula 1: Após colar a fórmula, pressione Entrar chave.

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

Fórmula 2: Após colar a fórmula, pressione Ctrl + Shift + Enter chaves.

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

Resultado:

notas:

  • Nas fórmulas acima:
    • A2: A12 = G1 significa pesquisar os critérios de G1 no intervalo A2:A12;
    • B2: B12 = G2 meios para pesquisar os critérios de G2 no intervalo B2:B12;
    • D2: D12 is o intervalo do qual você deseja retornar o valor correspondente.
  • Se você tiver mais de dois critérios, basta juntar os outros critérios na fórmula, como:
    =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))
    =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))
 3.2.2 Valor de correspondência VLOOKUP com base em dois ou mais critérios com um recurso inteligente

Pode ser um desafio lembrar as fórmulas complexas acima que precisam ser aplicadas repetidamente, o que pode diminuir a eficiência do seu trabalho. No entanto, Kutools for Excel oferece um Pesquisa de várias condições recurso que permite retornar o resultado correspondente com base em uma ou mais condições com apenas alguns cliques.

  1. Clique Kutools > Super PROCURA > Pesquisa de várias condições para ativar esse recurso.
  2. Em seguida, especifique as operações na caixa de diálogo com base em seus dados.
Note: Para aplicar este recurso, você deve baixar Kutools para Excel com teste gratuito de 30 dias primeiramente.


3.3 VLOOKUP para retornar vários valores com um ou mais critérios

No Excel, a função VLOOKUP pesquisa um valor e retorna apenas o primeiro valor correspondente se houver vários valores correspondentes encontrados. Às vezes, você pode querer retornar todos os valores correspondentes em uma linha, em uma coluna ou em uma única célula. Esta seção falará sobre como retornar os vários valores correspondentes com uma ou mais condições em uma pasta de trabalho.

 3.3.1 VLOOKUP todos os valores correspondentes com base em uma ou mais condições horizontalmente

Supondo que você tenha uma tabela de dados que contenha país, cidade e nomes no intervalo A1:C14, e agora, você deseja retornar todos os nomes horizontalmente que são de "EUA", conforme a captura de tela mostrada abaixo. Para resolver esta tarefa, por favor clique aqui para obter o resultado passo a passo.

 3.3.2 VLOOKUP todos os valores correspondentes com base em uma ou mais condições verticalmente

Se você precisar procurar e retornar todos os valores correspondentes verticalmente com base em critérios específicos, conforme a captura de tela abaixo, por favor clique aqui para obter a solução em detalhes.

 3.3.3 PROCV todos os valores correspondentes com base em uma ou mais condições em uma única célula

Se você deseja procurar e retornar vários valores correspondentes em uma única célula com o separador especificado, a nova função do TEXTJOIN pode ajudá-lo a resolver este trabalho de forma rápida e fácil.

notas:


3.4 VLOOKUP para retornar a linha inteira de uma célula correspondente

Nesta seção, falarei sobre como recuperar a linha inteira de um valor correspondente usando a função VLOOKUP.

Passo 1: Aplique e preencha a seguinte fórmula

Copie ou digite a fórmula abaixo em uma célula em branco onde deseja exibir o resultado e pressione Entrar chave para obter o primeiro valor. Em seguida, arraste a célula da fórmula para a direita até que os dados de toda a linha sejam exibidos.

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

Resultado:

Agora, você pode ver que todos os dados da linha são retornados. Veja a captura de tela:
função doc vlookup 50 1

Nota: na fórmula acima:

  • F2 é o valor de pesquisa no qual você deseja retornar a linha inteira com base;
  • A1: D12 é o intervalo de dados do qual você deseja pesquisar o valor de pesquisa;
  • A1 indica o número da primeira coluna dentro do seu intervalo de dados;
  • FALSE indica pesquisa exata.

Dicas:

  • Se várias linhas forem encontradas com base no valor correspondente, para retornar todas as linhas correspondentes, aplique a fórmula abaixo e pressione Ctrl + Shift + Enter teclas juntas para obter o primeiro resultado. Em seguida, arraste a alça de preenchimento para a direita. E então, continue arrastando a alça de preenchimento pelas células para obter todas as linhas correspondentes. Veja a demonstração abaixo:
    =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),"")
    função doc vlookup 51 2

3.5 PROCV aninhado no Excel

Às vezes, pode ser necessário procurar valores que estão interligados em várias tabelas. Nesse caso, você pode aninhar várias funções VLOOKUP juntas para obter o valor final.

Por exemplo, tenho uma planilha que contém duas tabelas separadas. A primeira tabela lista todos os nomes de produtos junto com o vendedor correspondente. A segunda tabela lista o total de vendas de cada vendedor. Agora, se você deseja encontrar as vendas de cada produto, conforme mostrado na captura de tela a seguir, pode aninhar a função VLOOKUP para realizar essa tarefa.
função doc vlookup 53 1

A fórmula genérica para a função VLOOKUP aninhada é:

=VLOOKUP(VLOOKUP(lookup_value, table_array1, col_index_num1, 0), table_array2, col_index_num2, 0)

Nota:

  • lookup_value é o valor que você procura;
  • Tabela_matriz1, Tabela_matriz2 são as tabelas nas quais existem o valor de pesquisa e o valor de retorno;
  • col_index_num1 indica o número da coluna na primeira tabela para encontrar os dados comuns intermediários;
  • col_index_num2 indica o número da coluna na segunda tabela que você deseja que retorne o valor correspondente;
  • 0 é usado para uma correspondência exata.

Passo 1: Aplique e preencha a seguinte fórmula

Aplique a seguinte fórmula em uma célula em branco e, em seguida, arraste a alça de preenchimento para as células nas quais deseja aplicar esta fórmula.

=VLOOKUP(VLOOKUP(G3,$A$3:$B$7,2,0),$D$3:$E$7,2,0)

Resultado:

Agora, você obterá o resultado conforme mostrado na captura de tela a seguir:

Nota: na fórmula acima:

  • G3 contém o valor que você está procurando;
  • A3: B7, D3: E7 são os intervalos da tabela em que o valor de pesquisa e o valor de retorno existem;
  • 2 é o número da coluna no intervalo do qual retornar o valor correspondente.
  • 0 indica VLOOKUP matemática exata.

3.6 Verifique se o valor existe com base em uma lista de dados em outra coluna

A função VLOOKUP também pode ajudá-lo a verificar se existem valores com base na lista de dados em outra coluna. Por exemplo, se você quiser procurar os nomes na coluna C e apenas retornar Sim ou Não se o nome for encontrado ou não na coluna A, conforme a captura de tela abaixo.
função doc vlookup 56 1

Passo 1: Aplique e preencha a seguinte fórmula

Aplique a seguinte fórmula em uma célula em branco e, em seguida, arraste a alça de preenchimento para baixo até as células que deseja preencher com esta fórmula.

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

Resultado:

E você obterá o resultado que precisa, veja a captura de tela:

Nota: na fórmula acima:

  • C2 é o valor de pesquisa que você deseja verificar;
  • A2: A10 é a lista de intervalos de onde verificar se os valores de pesquisa serão encontrados ou não;
  • FALSE indica para obter uma correspondência exata.

3.7 VLOOKUP e soma todos os valores correspondentes em linhas ou colunas

Ao trabalhar com dados numéricos, pode ser necessário extrair valores correspondentes de uma tabela e somar os números em várias colunas ou linhas. Esta seção apresentará algumas fórmulas que podem ajudá-lo a realizar essa tarefa.

 3.7.1 VLOOKUP e soma todos os valores correspondentes em uma linha ou várias linhas

Suponha que você tenha uma lista de produtos com vendas por vários meses, conforme mostrado na captura de tela a seguir. Agora, você precisa somar todos os pedidos em todos os meses com base nos produtos fornecidos.

Passo 1: Aplique e preencha a seguinte fórmula

Copie ou insira a seguinte fórmula em uma célula em branco e pressione Ctrl + Shift + Enter teclas juntas para obter o primeiro resultado. Em seguida, arraste a alça de preenchimento para baixo para copiar esta fórmula para outras células necessárias.

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

Resultado:

Todos os valores em uma linha do primeiro valor correspondente foram somados, veja a captura de tela:

Nota: na fórmula acima:

  • H2 é a célula que contém o valor que você está procurando;
  • A2: F9 é o intervalo de dados (sem cabeçalhos de coluna) que inclui o valor de pesquisa e os valores correspondentes;
  • 2,3,4,5,6 {} são números de coluna usados ​​para calcular o total do intervalo;
  • FALSE indica uma correspondência exata.

Dicas: Se você deseja somar todas as correspondências em várias linhas, use a seguinte fórmula:

  • =SUMPRODUCT(($A$2:$A$9=H2)*$B$2:$F$9)
 3.7.2 VLOOKUP e soma todos os valores correspondentes em uma coluna ou várias colunas

Se você quiser somar o valor total para os meses específicos, conforme mostrado na captura de tela abaixo. A função VLOOKUP normal pode não ajudá-lo, aqui você deve aplicar as funções SUM, INDEX e MATCH juntas para criar uma fórmula.

Etapa 1: aplique a seguinte fórmula

Aplique a fórmula abaixo em uma célula em branco e arraste a alça de preenchimento para baixo para copiar esta fórmula para outras células.

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

Resultado:

Agora, os primeiros valores correspondentes com base no mês específico em uma coluna foram somados, veja a captura de tela:

Nota: na fórmula acima:

  • H2 é a célula que contém o valor que você está procurando;
  • B1: F1 são os cabeçalhos das colunas que contêm o valor de pesquisa;
  • B2: F9 é o intervalo de dados que contém os valores numéricos que você deseja somar.

Dicas: Para VLOOKUP e somar todos os valores correspondentes em várias colunas, você deve usar a seguinte fórmula:

  • =SUMPRODUCT($B$2:$F$9*(($B$1:$F$1)=H2))
 3.7.3 VLOOKUP e soma os primeiros valores correspondentes ou todos os valores correspondentes com um recurso poderoso

Talvez as fórmulas acima sejam difíceis de lembrar, neste caso, vou recomendar um recurso poderoso - Pesquisa e Soma of Kutools for Excel, com esse recurso, você pode Vlookup e somar a primeira correspondência ou todos os valores correspondentes em linhas ou colunas da maneira mais fácil possível.

  1. Clique Kutools > Super PROCURA > LOOKUP e Soma para ativar esse recurso.
  2. Em seguida, especifique as operações na caixa de diálogo com base em sua necessidade.
Note: Para aplicar este recurso, você deve baixar Kutools para Excel com teste gratuito de 30 dias primeiramente.
 3.7.4 VLOOKUP e soma todos os valores correspondentes em linhas e colunas

Se você deseja somar os valores, quando você precisa combinar coluna e linha, por exemplo, para obter o valor total da camisola do produto no mês de março conforme a imagem abaixo mostrada.

Aqui, você pode usar a função SUMPRODCT para realizar esta tarefa.

Aplique a seguinte fórmula em uma célula e, em seguida, pressione Entrar chave para obter o resultado, consulte a imagem:

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

Nota: Na fórmula acima:

  • B2: F9 é o intervalo de dados que contém os valores numéricos que você deseja somar;
  • B1: F1 são os cabeçalhos de coluna que contêm o valor de pesquisa no qual você deseja somar com base;
  • I2 é o valor de pesquisa nos cabeçalhos de coluna que você está procurando;
  • A2: A9 são os cabeçalhos de linha que contêm o valor de pesquisa no qual você deseja somar com base;
  • H2 é o valor de pesquisa nos cabeçalhos de linha que você está procurando.

3.8 VLOOKUP para mesclar duas tabelas com base em colunas-chave

Em seu trabalho diário, ao analisar dados, você pode precisar reunir todas as informações necessárias em uma única tabela com base em uma ou mais colunas-chave. Para realizar esta tarefa, você pode usar as funções INDEX e MATCH em vez da função VLOOKUP.

 3.8.1 VLOOKUP para mesclar duas tabelas com base em uma coluna-chave

Por exemplo, você tem duas tabelas, a primeira tabela contendo os dados de produtos e nomes, e a segunda tabela contém os dados de produtos e pedidos, agora, você deseja combinar essas duas tabelas combinando a coluna de produto comum em uma tabela.

Passo 1: Aplique e preencha a seguinte fórmula

Aplique a seguinte fórmula em uma célula em branco. Em seguida, arraste a alça de preenchimento para baixo até as células nas quais deseja aplicar esta fórmula

=INDEX($F$2:$F$8, MATCH($A2, $E$2:$E$8, 0))

Resultado:

Agora, você obterá uma tabela mesclada com a coluna do pedido juntando-se à primeira tabela com base nos dados da coluna-chave.

Nota: Na fórmula acima:

  • A2 é o valor de pesquisa que você está procurando;
  • F2: F8 é o intervalo de dados que você deseja que retorne os valores correspondentes;
  • E2: E8 é o intervalo de pesquisa que contém o valor de pesquisa.
 3.8.2 VLOOKUP para mesclar duas tabelas com base em várias colunas-chave

Se as duas tabelas que você deseja unir tiverem várias colunas-chave, para mesclar as tabelas com base nessas colunas comuns, siga as etapas abaixo.

A fórmula genérica é:

=INDEX(lookup_table, MATCH(1, (lookup_value1=lookup_range1) * (lookup_value2=lookup_range2), 0), return_column_number)

Nota:

  • tabela de pesquisa é o intervalo de dados que contém os dados de pesquisa e os registros correspondentes;
  • valor_procurado1 é o primeiro critério que você está procurando;
  • intervalo_procurado1 é a lista de dados que contém o primeiro critério;
  • valor_procurado2 é o segundo critério que você está procurando;
  • intervalo_procurado2 é a lista de dados que contém o segundo critério;
  • return_column_number indica o número da coluna no lookup_table que você deseja retornar o valor correspondente.

Etapa 1: aplique a seguinte fórmula

Aplique a fórmula abaixo em uma célula em branco onde deseja colocar o resultado e pressione Ctrl + Shift + Enter chaves juntas para obter o primeiro valor correspondido, consulte a captura de tela:

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

Passo 2: Preencher a fórmula para outras células

Em seguida, selecione a primeira célula da fórmula e arraste a alça de preenchimento para copiar esta fórmula para outras células conforme necessário:

Tips: No Excel 2016 ou versões posteriores, você também pode usar o Power Query recurso para mesclar duas ou mais tabelas em uma com base em colunas-chave. Clique para saber os detalhes passo a passo.

3.9 VLOOKUP combinando valores em várias planilhas

Você já precisou executar um VLOOKUP em várias planilhas no Excel? Por exemplo, se você tiver três planilhas com intervalos de dados e quiser recuperar valores específicos com base em critérios dessas planilhas, siga o tutorial passo a passo Valores VLOOKUP em várias planilhas para realizar essa tarefa.


Os valores correspondentes de VLOOKUP mantêm a formatação da célula

Ao pesquisar valores correspondentes, a formatação original da célula, como cor da fonte, cor de fundo, formato de dados etc., não será mantida. Para manter a formatação da célula ou dos dados, esta seção apresentará alguns truques para resolver os trabalhos.

4.1 VLOOKUP correspondendo ao valor e mantendo a cor da célula, a formatação da fonte

Como todos sabemos, a função VLOOKUP normal só pode recuperar o valor correspondente de outro intervalo de dados. No entanto, pode haver casos em que você gostaria de obter o valor correspondente junto com a formatação da célula, como a cor de preenchimento, a cor da fonte e o estilo da fonte. Nesta seção, discutiremos como recuperar valores correspondentes preservando a formatação de origem no Excel.

Siga as etapas a seguir para pesquisar e retornar seu valor correspondente junto com a formatação da célula:

Passo 1: Copie o código 1 para o módulo Sheet Code

  1. Na planilha contém os dados que você deseja VLOOKUP, clique com o botão direito na guia da planilha e selecione Ver código no menu de contexto. Veja a imagem:
  2. No aberto Microsoft Visual Basic para Aplicações janela, copie o código VBA abaixo para a janela de código.
  3. Código VBA 1: VLOOKUP para obter a formatação da célula junto com o valor de pesquisa
  4. Sub Worksheet_Change(ByVal Target As Range)
    'Updateby Extendoffice
        Dim I As Long
        Dim xKeys As Long
        Dim xDicStr As String
        On Error Resume Next
        Application.ScreenUpdating = False
        xKeys = UBound(xDic.Keys)
        If xKeys >= 0 Then
            For I = 0 To UBound(xDic.Keys)
                xDicStr = xDic.Items(I)
                If xDicStr <> "" Then
                    Range(xDic.Keys(I)).Interior.Color = _
                    Range(xDic.Items(I)).Interior.Color
                    Range(xDic.Keys(I)).Font.FontStyle = _
                    Range(xDic.Items(I)).Font.FontStyle
                    Range(xDic.Keys(I)).Font.Size = _
                    Range(xDic.Items(I)).Font.Size
                    Range(xDic.Keys(I)).Font.Color = _
                    Range(xDic.Items(I)).Font.Color
                    Range(xDic.Keys(I)).Font.Name = _
                    Range(xDic.Items(I)).Font.Name
                    Range(xDic.Keys(I)).Font.Underline = _
                    Range(xDic.Items(I)).Font.Underline
                Else
                    Range(xDic.Keys(I)).Interior.Color = xlNone
                End If
            Next
            Set xDic = Nothing
        End If
        Application.ScreenUpdating = True
    End Sub
    

Etapa 2: Copie o código 2 na janela do módulo

  1. Ainda na Microsoft Visual Basic para Aplicações janela, clique em inserção > Móduloe, em seguida, copie o código 2 do VBA abaixo na janela Módulo.
  2. Código VBA 2: VLOOKUP para obter a formatação da célula junto com o valor de pesquisa
  3. Public xDic As New Dictionary
    Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
        Dim xFindCell As Range
        On Error Resume Next
        Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
        If xFindCell Is Nothing Then
            LookupKeepFormat = ""
            xDic.Add Application.Caller.Address, ""
        Else
            LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
            xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
        End If
    End Function
    

Passo 3: Selecione a opção para VBAproject

  1. Após inserir os códigos acima, clique em Ferramentas > Referências no Microsoft Visual Basic para Aplicações janela. Em seguida, verifique o Tempo de execução de scripts da Microsoft caixa de seleção no Referências - VBAProject caixa de diálogo. Veja as capturas de tela:
  2. Então clique OK para fechar a caixa de diálogo e, em seguida, salve e feche a janela de código.

Etapa 4: digite a fórmula para obter o resultado

  1. Agora, volte para a planilha, aplique a seguinte fórmula. Em seguida, arraste a alça de preenchimento para baixo para obter todos os resultados junto com sua formatação. Veja a captura de tela:
    =LookupKeepFormat(E2,$A$1:$C$10,3)

Nota: na fórmula acima:

  • E2 é o valor que você vai procurar;
  • A1: C10 é o intervalo da tabela;
  • 3 é o número da coluna da tabela da qual você deseja recuperar o valor correspondente.

4.2 Manter o formato de data de um valor retornado VLOOKUP

Ao usar a função VLOOKUP para pesquisar e retornar um valor com formato de data, o resultado retornado pode ser exibido como um número. Para manter o formato de data no resultado retornado, você deve incluir a função PROCV dentro da função TEXTO.

Passo 1: Aplique e preencha a seguinte fórmula

Por favor, aplique a fórmula abaixo em uma célula em branco. Em seguida, arraste a alça de preenchimento para copiar esta fórmula para outras células.

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

Resultado:

Todas as datas correspondentes foram retornadas conforme a captura de tela abaixo:

Nota: Na fórmula acima:

  • E2 é o valor de pesquisa;
  • A2: C9 é o intervalo de pesquisa;
  • 3 é o número da coluna que você deseja que o valor seja retornado;
  • FALSE indica para obter uma correspondência exata;
  • mm/dd/yyy é o formato de data que você deseja manter.

4.3 Retornar o comentário da célula do VLOOKUP

Você já precisou recuperar os dados da célula correspondente e seu comentário associado usando VLOOKUP no Excel, conforme mostrado na captura de tela a seguir? Nesse caso, a função definida pelo usuário fornecida abaixo pode ajudá-lo a realizar essa tarefa.

Passo 1: Copie o código em um Módulo

  1. Mantenha pressionada a ALT + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.
  2. Clique inserção > Móduloe, em seguida, copie e cole o código a seguir na janela do módulo.
    Código VBA: Vlookup e valor correspondente de retorno com comentário de célula:
    Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
    'Updateby Extendoffice
        Application.Volatile
        Dim xRet As Variant 'could be an error
        Dim xCell As Range
        xRet = Application.Match(LookVal, FTable.Columns(1), FType)
        If IsError(xRet) Then
            VlookupComment = "Not Found"
        Else
            Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
            VlookupComment = xCell.Value
            With Application.Caller
                If Not .Comment Is Nothing Then
                    .Comment.Delete
                End If
                If Not xCell.Comment Is Nothing Then
                    .AddComment xCell.Comment.Text
                End If
            End With
        End If
    End Function
  3. Em seguida, salve e feche a janela de código.

Etapa 2: digite a fórmula para obter o resultado

  1. Agora, insira a seguinte fórmula e arraste a alça de preenchimento para copiar esta fórmula para outras células. Ele retornará os valores correspondentes e os comentários simultaneamente, veja a captura de tela:
    =vlookupcomment(D2,$A$2:$B$9,2,FALSE)

Nota: Na fórmula acima:

  • D2 é o valor de pesquisa que você deseja que retorne seu valor correspondente;
  • A2: B9 é a tabela de dados que você deseja usar;
  • 2 é o número da coluna que contém o valor correspondente que você deseja retornar;
  • FALSE indica para obter uma correspondência exata.

4.4 Números VLOOKUP armazenados como texto

Por exemplo, eu tenho um intervalo de dados em que o número de ID na tabela original está no formato numérico e o número de ID nas células de pesquisa é armazenado como texto, você pode encontrar um erro #N/A ao usar a função VLOOKUP normal. Nesse caso, para recuperar as informações corretas, você pode agrupar as funções TEXTO e VALOR na função VLOOKUP. Abaixo está a fórmula para conseguir isso:

Passo 1: Aplique e preencha a seguinte fórmula

Aplique a seguinte fórmula em uma célula em branco e arraste a alça de preenchimento para baixo para copiar esta fórmula.

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

Resultado:

Agora, você obterá os resultados corretos conforme a captura de tela abaixo:

notas:

  • Na fórmula acima:
    • D2 é o valor de pesquisa que você deseja que retorne seu valor correspondente;
    • A2: B8 é a tabela de dados que você deseja usar;
    • 2 é o número da coluna que contém o valor correspondente que você deseja retornar;
    • 0 indica para obter uma correspondência exata.
  • Esta fórmula também funciona bem se você não tiver certeza de onde tem números e onde tem texto.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations