Como procurar ou encontrar valores noutra pasta de trabalho?

Configurar referências de procura com VLOOKUP pode ser confuso, especialmente se alterar frequentemente o caminho, nome ou folha do ficheiro de origem. Nestes cenários, automatizar o processo de procura com VBA pode ser uma solução mais simplificada, permitindo procurar valores mesmo quando o livro de origem está fechado e automatizando a seleção de intervalos e a devolução de dados.

Siga estes passos para utilizar VBA em procuras entre livros:

1. Prima Alt + F11 simultaneamente para abrir a janela do editor Microsoft Visual Basic para Aplicações.

2.No editor VBA, clique em Inserir>Módulo, depois copie e cole o seguinte código na janela do módulo:

VBA: Procurar dados e Valor de retorno de outro livro fechado

Option Explicit

' Convert column number to column letter
Private Function GetColumn(ByVal Num As Integer) As String
    If Num <= 26 Then
        GetColumn = Chr(Num + 64)
    Else
        GetColumn = Chr((Num - 1) \ 26 + 64) & _
                    Chr((Num - 1) Mod 26 + 65)
    End If
End Function

Sub FindValue()

    Dim xAddress As String
    Dim xString As String
    Dim xFileName As Variant
    Dim xUserRange As Range
    Dim xRg As Range
    Dim xFCell As Range
    Dim xSourceSh As Worksheet
    Dim xSourceWb As Workbook
    
    On Error Resume Next
    
    ' Get current selection address
    xAddress = Application.ActiveWindow.RangeSelection.Address
    
    ' Ask user to select lookup range
    Set xUserRange = Application.InputBox( _
        Prompt:="Lookup values :", _
        Title:="Kutools for Excel", _
        Default:=xAddress, _
        Type:=8)
    
    If Err.Number <> 0 Then Exit Sub
    On Error GoTo 0
    
    ' Limit selection to used range
    Set xUserRange = Application.Intersect(xUserRange, _
                                           Application.ActiveSheet.UsedRange)
    
    ' Ask user to select source workbook
    xFileName = Application.GetOpenFilename( _
                "Excel Files (*.xlsx), *.xlsx", _
                1, _
                "Select a Workbook")
                
    If xFileName = False Then Exit Sub
    
    Application.ScreenUpdating = False
    
    ' Open source workbook
    Set xSourceWb = Workbooks.Open(xFileName)
    Set xSourceSh = xSourceWb.Worksheets.Item(1)
    
    ' Build external reference string
    xString = "='" & xSourceWb.Path & Application.PathSeparator & _
              "[" & xSourceWb.Name & "]" & _
              xSourceSh.Name & "'!$"
    
    ' Loop through user range
    For Each xRg In xUserRange
    
        ' Find matching value in source sheet
        Set xFCell = xSourceSh.Cells.Find( _
                        What:=xRg.Value, _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        MatchCase:=False)
        
        ' If found, write formula 2 columns to the right
        If Not xFCell Is Nothing Then
            xRg.Offset(0, 2).Formula = _
                xString & _
                GetColumn(xFCell.Column + 1) & _
                "$" & xFCell.Row
        End If
        
    Next xRg
    
    ' Close source workbook without saving
    xSourceWb.Close False
    
    Application.ScreenUpdating = True

End Sub

Detalhes importantes:

• O código devolve o valor correspondente numa coluna deslocada 2 colunas em relação ao intervalo de procura. Por exemplo, se selecionar a coluna B, os resultados aparecem na coluna D.
• Se pretender que o resultado apareça noutra coluna, altere o número 2 em xRg.Offset(0,2).Formulapara outro valor (por exemplo,)1 para a coluna seguinte ou 3 para a terceira coluna à direita).
• Escolha o livro e a folha corretos quando solicitado; o código utilizará sempre a primeira folha do ficheiro selecionado. Ajuste o código se a sua folha de origem não for a primeira.
• Guarde sempre o seu ficheiro antes de executar macros desconhecidas. As macros não podem ser anuladas após a execução.

3. Execute a macro premindo a tecla F5 ou clicando no botão Executar. Aparecerá uma caixa de diálogo intitulada «Kutools para Excel» que lhe pedirá para selecionar o intervalo de células cujos valores pretende procurar.

4. Após selecionar o seu intervalo, clique em OK. Pouco depois, aparecerá outra caixa de diálogo a solicitar que escolha o livro de origem (mesmo que esteja fechado). Navegue até ao ficheiro adequado, selecione-o e, em seguida, clique em Abrir para confirmar.

Quando a macro terminar, os valores correspondentes do livro de origem serão devolvidos à coluna de destino no seu Planilha Atual. Se alguns valores estiverem em falta, verifique se os Intervalo de valor de pesquisa no Planilha Atual correspondem exatamente aos do Dados de Origem (maiúsculas/minúsculas e espaços à esquerda/Espaços Atrás são relevantes para uma correspondência exata).

Vantagens: Funciona com livros fechados, evita caminhos de arquivo codificados nas fórmulas e oferece flexibilidade na seleção de ficheiros de origem em tempo real.
Considerações: As macros têm de estar ativadas; o VBA pode não funcionar em folhas protegidas ou com dados provenientes de ficheiros que não sejam do Excel. Guarde o seu livro com suporte a macros (*.xlsm) se pretender utilizar este método frequentemente.

Se encontrar erros, certifique-se de que não há gralhas nos nomes das folhas ou ficheiros, de que o intervalo selecionado é adequado e de que os caminhos dos ficheiros estão acessíveis. Para depuração, considere executar o código linha a linha no editor do VBA.

Além da abordagem clássica com VLOOKUP e do VBA, existem formas alternativas de realizar pesquisas entre livros no Excel — ideais em situações específicas, como quando a estrutura dos seus dados é diferente, prefere fórmulas a macros ou precisa de maior flexibilidade, por exemplo, para procurar para a esquerda ou aplicar múltiplos critérios.

Utilização das funções ÍNDICE e CORRESP entre livros

Com a combinação de ÍNDICE e CORRESP, pode procurar valores em qualquer direção — esquerda, direita, acima ou abaixo — noutro livro. Isto é especialmente útil quando a coluna de onde pretende obter dados não está à direita da coluna de procura (uma limitação do VLOOKUP).

Cenário: Imagine que pretende obter o preço de outra tabela aberta, em que o nome da fruta pode não estar na primeira coluna.

1.No seu livro de destino, selecione a célula onde pretende apresentar o resultado (por exemplo, C2) e introduza a fórmula abaixo (substitua o livro, a folha e o intervalo conforme necessário):

=INDEX([Price.xlsx]Sheet1!$B$1:$B$24, MATCH(B2, [Price.xlsx]Sheet1!$A$1:$A$24,0))

2. Prima Enter. Depois, copie ou preencha a fórmula nas outras linhas conforme necessário, arrastando a alça de preenchimento.

Explicação dos parâmetros:

• [Price.xlsx]Sheet1!$B$1:$B$24: O intervalo onde os preços estão armazenados.
• B2: O nome da fruta a procurar.
• [Price.xlsx]Sheet1!$A$1:$A$24: O intervalo onde procurar o seu valor de pesquisa.
• O 0 no final garante uma correspondência exata.

Pontos fortes: Funciona tanto para pesquisas à esquerda como à direita; adapta-se a disposições mais flexíveis.
Dicas: Evite mover ou renomear ficheiros de origem sem atualizar a respetiva fórmula.

Utilização do XLOOKUP para procura entre livros (Excel 365 e posterior)

Se utilizar Excel 365 ou Excel 2021, a nova função XLOOKUP é ainda mais flexível. Permite encontrar facilmente correspondências exatas, suporta procuras à esquerda e trata automaticamente valores em falta sem gerar erros.

Para a utilizar:
Na célula onde pretende o resultado, introduza:

=XLOOKUP(B2, [Price.xlsx]Sheet1!$A$1:$A$24, [Price.xlsx]Sheet1!$B$1:$B$24, "Not found")

Prima Enter e copie a fórmula conforme necessário. Aqui, «Não encontrado» pode ser substituído por qualquer texto personalizado que deseje apresentar caso a procura falhe.

Benefícios: Mais flexível do que o VLOOKUP e mais fácil de gerir; evita muitos erros comuns associados a fórmulas antigas. Contudo, o XLOOKUP só está disponível em versões mais recentes do Excel.

Para requisitos complexos, como correspondência com múltiplos critérios, procura em folhas combinadas ou evitar problemas de desempenho em ficheiros muito grandes, considere organizar os Dados de Origem em tabelas estruturadas ou utilizar a ferramenta Power Query do Excel para Vincular Dados eficientemente entre vários livros.