VBA – VLOOKUP ou XLOOKUP em Outra Planilha ou Pasta de Trabalho

Este artigo demonstrará como usar as funções VLOOKUP e XLOOKUP para pesquisar valores em outras planilhas ou pastas de trabalho no VBA.

As funções VLOOKUP e XLOOKUP no Excel são funções úteis que nos permitem pesquisar um valor correspondente em um intervalo e retornar um valor correspondente de outra coluna. Essas funções podem ser usadas diretamente em uma fórmula no Excel ou podem ser usadas na codificação VBA com o método WorksheetFunction. Podemos usar essas funções para procurar valores contidos em uma planilha diferente daquela em que queremos que o resultado seja produzido, ou até mesmo em uma pasta de trabalho diferente.

VLOOKUP e XLOOKUP de Outra Planilha

VLOOKUP de Outra Planilha

Para usar o VLOOKUP em uma planilha diferente dos dados de pesquisa, podemos digitar o seguinte código:

Sub LookupPreco()
   ActiveCell = Application.WorksheetFunction.VLookup(Range("B3"), Sheets("ListaProdutos").Range("B2:C6"), 2, False)
End Sub

OU

Sub LookupPreco() 
   ActiveCell = Application.VLookup(Range("B3"), Sheets("ListaProdutos").Range("B2:C6"), 2, False) 
End Sub

Resultando no seguinte:

Também poderíamos, para facilitar o uso, usar variáveis em nosso código.

Sub LookupPreco()
   Dim ws As Worksheet
   Dim rng As Range
   Dim strProduct As String
   strProduct = Range("B3")
   Set ws = Sheets(1)
   Set rng = ws.Range("B2:C6")
   ActiveCell = Application.WorksheetFunction.VLookup(strProduct, rng, 2, False)
End Sub

Isso retornaria o mesmo resultado.

Se estivermos procurando um valor que não existe, talvez seja melhor usar a função Application.VLookup para retornar #N/A à célula, em vez da função Application.WorksheetFunction.VLookup, que resultaria em um erro do VBA.

Se quisermos retornar a fórmula em vez do valor para a célula de destino, então este é o código que precisaremos inserir.

Sub LookupPreco()
   ActiveCell = "=VLOOKUP(RC[-1],ListaProdutos!RC[-1]:R[3]C,2,FALSE)"
End Sub

Onde a fórmula está usando a sintaxe R1C1 (linhas e colunas) em vez da sintaxe A1 (intervalo).

Isso resultaria em:

XLOOKUP de Outra Planilha

Usar o XLOOKUP para procurar dados em uma planilha diferente é praticamente a mesma coisa. Lembre-se de que o XLOOKUP examina vários intervalos em vez de uma coluna específica para encontrar o valor.

Sub LookupPreco()
   ActiveCell = Application.WorksheetFunction.XLookup(Range("B3"), Sheets("ListaProdutos").Range("B2:B6"), Sheets("ListaProdutos").Range("C2:C6"))
End Sub

ou com variáveis:

Sub LookupPreco()
  Dim ws As Worksheet
  Dim strProduct As String
  Dim rngProduct As Range
  Dim rngPrice As Range
  strProduct = Range("B3")
  Set ws = Sheets("ListaProdutos")
  Set rngProduct = ws.Range("B2:B6")
  Set rngPrice = ws.Range("C2:C6")
  ActiveCell = Application.WorksheetFunction.XLookup(strProduct, rngProduct, rngPrice)
End Sub

O resultado de qualquer um desses exemplos seria, então, o seguinte:

Para retornar uma fórmula usando XLOOKUP, precisaríamos mais uma vez usar a sintaxe de linha/coluna (R1C1) em vez da sintaxe de intervalo (A1).

Sub EntrarFormula()
  ActiveCell.FormulaR1C1 = "=XLOOKUP(RC[-1],ListaProdutos!RC[-1]:R[3]C[-1],ListaProdutos!RC:R[3]C)"
End Sub

O resultado disso seria:

Uma das principais vantagens do uso do XLOOKUP em relação ao VLOOKUP é a capacidade de pesquisar um intervalo de colunas e retornar o valor em cada coluna.

Vejamos o exemplo a seguir:

Criamos a fórmula na célula C3, onde ela está procurando os valores no intervalo C2:E6. Devido ao fato de serem várias colunas, ela preencherá automaticamente as colunas D e E com os resultados correspondentes encontrados. A fórmula transborda para as colunas D e E sem que tenhamos que usar CTRL+SHIFT para uma fórmula de matriz – essa capacidade da fórmula de transbordar é uma das novas adições do Excel 365.

Para replicar isso com o código VBA, podemos digitar o seguinte em nossa macro:

Sub LookupPreco()
   ActiveCell.Formula2R1C1 = "=XLOOKUP(RC[-1],ListaProdutos!RC[-1]:R[3]C[-1],ListaProdutos!RC:R[3]C[2])"
End Sub

Onde a fórmula está usando a sintaxe R1C1 (linhas e colunas) em vez da sintaxe A1 (intervalo). Isso fará com que as fórmulas sejam inseridas no Excel conforme mostrado no gráfico acima.

Não é possível pesquisar várias colunas se você estiver usando o método WorksheetFunction.

VLOOKUP e XLOOKUP de Outra Pasta de Trabalho

Trabalhar com outra pasta de trabalho em vez de uma planilha diferente é praticamente a mesma coisa.

VLOOKUP de Outra Pasta de Trabalho

Considere o seguinte exemplo:

Para replicar isso com o código VBA, criaríamos a macro a seguir:

Sub LookupPreco()    
  ActiveCell = Application.WorksheetFunction.VLookup(Range("B3"), Workbooks("Produtos.xlsm").Sheets("ListaProdutos").Range("B2:C6"), 2, False)
End Sub

onde o arquivo Products.xlsm precisaria estar aberto no Excel para que essa pesquisa funcionasse.

Também poderíamos usar variáveis:

Sub LookupPreco()
  Dim wb As Workbook
  Dim ws As Worksheet
  Dim rng As Range
  Dim strProduct As String
  Set wb = Workbooks("Produtos.xlsm")
  Set ws = wb.Sheets("ListaProdutos")
  Set rng = ws.Range("B2:C6")
  strProduct = Range("B3")
  ActiveCell = Application.WorksheetFunction.VLookup(strProduct, rng, 2, False)
End Sub

O resultado de qualquer um desses exemplos seria:

 

 

Se quiséssemos que a fórmula real aparecesse na célula em vez do valor, nosso código VBA incorporaria o nome da pasta de trabalho, bem como o nome ou o número da planilha.

Por exemplo:

Sub LookupPreco()
  ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],[Produtos.xlsm]ListaProdutos!R3C2:R6C3,2,FALSE)"
End Sub

XLOOKUP de Outra Pasta de Trabalho

A vinculação a outro arquivo usando o XLOOKUP em vez do VLOOKUP segue as mesmas linhas.

Para retornar o valor ao Excel:

Sub LookupPreco()
   ActiveCell = Application.WorksheetFunction.XLookup(Range("B3"),  Workbooks("Produtos.xlsm").Sheets("ListaProdutos").Range("B2:B6"), Workbooks("Produtos.xlsm").Sheets("ListaProdutos").Range("C2:C6"))
End Sub

ou com variáveis:

Sub LookupPreco()
  Dim wb as Workbook
  Dim ws As Worksheet
  Dim strProduct As String
  Dim rngProduct As Range
  Dim rngPrice As Range
  strProduct = Range("B3")
  Set wb = Workbooks("Produtos.xlsm")
  Set ws = wb.Sheets("ListaProdutos")
  Set rngProduct = ws.Range("B2:B6")
  Set rngPrice = ws.Range("C2:C6")
  ActiveCell = Application.WorksheetFunction.XLookup(strProduct, rngProduct, rngPrice)
End Sub

O resultado de qualquer um desses exemplos seria, então, o seguinte:

Para retornar a fórmula ao Excel em vez do valor, podemos alterar nossa macro para incluir o nome da pasta de trabalho, bem como o nome ou o número da planilha.

Sub LookupPreco()
   ActiveCell.Formula2R1C1 = "=XLOOKUP(RC[-1],[Produtos.xlsm]ListaProdutos!RC[-1]:R[3]C[-1],[Produtos.xlsm]ListaProdutos!RC:R[3]C[2])"
End Sub