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:

Argumentos:

Notas de função:

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)

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:

 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:

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:

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.

 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:

 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.

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:

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:

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:

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)

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:

 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.

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.

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:

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.

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

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:

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.

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:

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:

 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:

 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.

 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))

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.

 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 é:

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:

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
   

5. 

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
   

4. 

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)

   
   

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:

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)

   
   

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: