PROCV entre duas pastas de trabalho: guia passo a passo fácil

Se você estiver trabalhando com várias pastas de trabalho no Excel, pode ser necessário usar a função VLOOKUP para recuperar dados de uma pasta de trabalho para outra.

A função VLOOKUP procura um valor específico em uma tabela em uma pasta de trabalho separada e retorna um valor correspondente da mesma linha. A sintaxe deve incluir uma referência totalmente qualificada à segunda pasta de trabalho e ao nome da planilha.

Obter as referências corretas pode ser um desafio quando você está usando a função pela primeira vez. Este artigo usa exemplos de planilhas para mostrar exatamente como inserir as fórmulas.

Você também aprenderá algumas melhorias no VLOOKUP e várias alternativas para a função.

Vamos começar!

Índice

• O que é VLOOKUP?
• Como usar VLOOKUP entre duas pastas de trabalho
  • Dados de amostra
  • Etapa 1: manter as duas pastas de trabalho abertas
  • Etapa 2: comece a inserir a fórmula VLOOKUP
  • Etapa 3: como referenciar a segunda pasta de trabalho
• 3 erros comuns de VLOOKUP e como corrigi-los
  • 1. Erro #N/D
  • 2. #Ref! Erro de número de coluna
  • 3. Erro de aspas e colchetes
• 4 dicas para gerenciar pastas de trabalho diferentes
• Como atualizar dados automaticamente
• Usos avançados da função VLOOKUP
• 2 principais alternativas ao VLOOKUP
  • 1. CORRESPONDÊNCIA DE ÍNDICE
  • 2. XLOOKUP
• Como usar o VLOOKUP em várias planilhas
  • Etapa 1: identificar os componentes
  • Etapa 2: configurar seus dados
  • Etapa 3: use a função VLOOKUP
  • (Opcional) Etapa 4: solucionar problemas de erros #N/A
• Pensamentos finais

O que é VLOOKUP?

Suponha que você tenha uma pasta de trabalho de vendas de produtos e uma pasta de trabalho separada com os preços de cada produto. Você deseja acessar os preços da segunda pasta de trabalho e usá-los nos cálculos da primeira pasta de trabalho.

VLOOKUP é uma solução para esta tarefa. O termo significa “Vertical Lookup . “

É uma função incorporada que permite pesquisar um valor específico em uma coluna e retornar um valor correspondente em outra coluna com base no local.

Pode ser usado com:

• uma única planilha

• várias planilhas na mesma pasta de trabalho

• duas pastas de trabalho do Excel

Se você não tiver a complexidade adicional de várias pastas de trabalho, confira este tutorial sobre como comparar duas colunas no Excel usando VLOOKUP .

Como usar VLOOKUP entre duas pastas de trabalho

A função VLOOKUP é especialmente útil quando você precisa pesquisar dados em várias planilhas ou pastas de trabalho.

Os três principais passos para fazer isso são:

1. Abra ambas as pastas de trabalho.

2. Insira a fórmula VLOOKUP na primeira pasta de trabalho do Excel.

3. Forneça uma referência totalmente qualificada para a segunda pasta de trabalho.

Dados de amostra

Este tutorial simples trabalha com exemplos baseados em dados de amostra. Para acompanhar, crie uma nova pasta de trabalho chamada “Vendas” e insira esses dados nas colunas A e B:

• Maçãs, 40

• Laranjas, 30

• Peras, 70

• Bananas, 20

Adicione uma terceira coluna para conter as informações de preço:

Crie uma segunda pasta de trabalho do Excel chamada “Produto” e renomeie Plan1 como “Preços”. Insira esses dados na coluna A e na coluna B:

• Maçãs, 1,50

• Laranjas, 2.30

• Peras, 3,60

• Bananas, 2.10

Os dados ficam assim:

Para calcular a receita das vendas na primeira planilha, precisamos procurar o preço na segunda planilha. VLOOKUP para o resgate!

Etapa 1: manter as duas pastas de trabalho abertas

Ambas as pastas de trabalho devem estar abertas ao usar a fórmula VLOOKUP entre duas pastas de trabalho. Se a segunda pasta de trabalho for fechada, a função retornará um erro.

Você pode abri-los separadamente ou usar o comando “Abrir” no Excel para abri-los de uma só vez. Há duas maneiras de abrir qualquer pasta de trabalho:

• Vá para a guia Arquivo e selecione "Abrir".

• Use o atalho de teclado “Ctrl + O” .

Depois de abrir as duas pastas de trabalho, você pode alternar entre elas clicando na guia apropriada na parte inferior da janela do Excel.

Etapa 2: comece a inserir a fórmula VLOOKUP

Aqui está a sintaxe básica da função VLOOKUP:

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

• lookup_value : o valor que você deseja pesquisar na outra pasta de trabalho.

• table_array : O intervalo de células na outra pasta de trabalho que contém os dados que você deseja recuperar.

• col_index_num : O número da coluna no intervalo que contém os dados que você deseja recuperar.

• range_lookup : Este último parâmetro é opcional. Se você defini-lo como VERDADEIRO ou omiti-lo, VLOOKUP procurará uma correspondência aproximada. Se você defini-lo como FALSE, VLOOKUP procurará apenas uma correspondência exata.

Digamos que você queira pesquisar o preço das maçãs na segunda pasta de trabalho de nossos dados de exemplo. É assim que a fórmula será dividida:

• O valor_procurado está na célula A2 da primeira pasta de trabalho.

• A matriz da tabela está no intervalo de células A1:B5 da segunda pasta de trabalho.

• O col_index_num é a segunda coluna que contém o preço.

A parte mais complicada da fórmula VLOOKUP é referenciar a segunda pasta de trabalho corretamente.

Etapa 3: como referenciar a segunda pasta de trabalho

Para concluir a fórmula VLOOKUP para duas pastas de trabalho, você precisará usar uma referência totalmente qualificada que inclua estes detalhes da segunda pasta de trabalho:

• nome da pasta de trabalho

• nome da folha

• intervalo de células

Isso garante que o Excel saiba exatamente onde encontrar os dados que você está procurando. A referência totalmente qualificada é usada para o argumento table_array da função VLOOKUP.

Estes são os valores para os dados de amostra:

• nome da pasta de trabalho: Product.xlsx

• nome da planilha: Preços

• intervalo de células: $A1:$B5

Para pesquisar o preço do primeiro item, insira a seguinte fórmula na célula C2 da primeira pasta de trabalho:

=PROCV(A2, [Produto.xlsx]Preços!$A$1:$B$5, 2, FALSO)

Esta imagem mostra a função em ação na barra de fórmulas da primeira célula:

Observe que o nome da pasta de trabalho está entre colchetes e o nome da planilha é seguido por um ponto de exclamação.

Se o nome do arquivo do Excel contiver um espaço ou caracteres especiais, você deverá colocar toda a pasta de trabalho e planilha entre aspas simples.

A fórmula usa valores absolutos para o intervalo de células (o prefixo $). Isso permite que você copie a fórmula para o restante das linhas clicando na alça de preenchimento.

Aqui estão os resultados do VLOOKUP:

E é isso! Agora você sabe como usar VLOOKUP de forma eficaz. Para ajudá-lo caso encontre algum erro, compilamos três dos erros de PROCV mais comuns na próxima seção.

3 erros comuns de VLOOKUP e como corrigi-los

Agora que você aprendeu a usar o VLOOKUP de outra pasta de trabalho, pode encontrar alguns erros comuns.

Aqui estão os três erros mais frequentes e como corrigi-los.

1. Erro #N/D

O erro #N/A ocorre quando o valor de pesquisa não é encontrado na matriz da tabela. Para corrigir esse erro, você pode tentar o seguinte:

• Verifique a ortografia do valor de pesquisa.

• Certifique-se de que o valor de pesquisa esteja na primeira coluna da matriz da tabela.

• Verifique se o valor de pesquisa não está formatado como texto nas duas planilhas.

• Certifique-se de que o argumento range_lookup esteja definido como FALSE para localizar uma correspondência exata.

O exemplo acima falhou ao localizar um valor correspondente para Cherries na segunda pasta de trabalho.

2. #Ref! Erro de número de coluna

O # Ref! O erro ocorre quando o argumento col_index_num é maior que o número de colunas na matriz da tabela.

Para corrigir esse erro, verifique se o argumento col_index_num é menor ou igual ao número de colunas na tabela de origem.

Na figura abaixo, o col_index_num foi definido como 3, mas há apenas duas colunas no intervalo de referência.

3. Erro de aspas e colchetes

O erro de aspas e colchetes ocorre quando você executa VLOOKUP com um valor contendo aspas ou colchetes.

Para corrigir esse erro, você pode tentar o seguinte:

• Use aspas duplas ao redor do valor de pesquisa.

• Use a função CHAR(34) para inserir aspas duplas.

• Use a função SEERRO para retornar uma célula em branco se o valor de pesquisa contiver aspas ou colchetes.

Depois de se familiarizar com esses erros, corrigi-los é simples! Para ajudá-lo a se tornar um profissional do VLOOKUP, fornecemos quatro dicas para gerenciar diferentes pastas de trabalho na próxima seção.

4 dicas para gerenciar pastas de trabalho diferentes

Ao trabalhar com várias pastas de trabalho, é importante controlar qual pasta de trabalho contém quais dados.

Aqui estão nossas quatro melhores dicas para manter sua sanidade:

1. Use convenções de nomenclatura para ajudá-lo a lembrar quais planilhas do Excel contêm quais dados.

2. Use referências absolutas em suas fórmulas para garantir que o intervalo de pesquisa faça referência às células corretas, mesmo que você mova ou copie a fórmula para outro local ou planilha do Excel.

3. Use a função SEERRO para lidar com quaisquer erros #N/A que podem ocorrer se os valores de pesquisa não forem encontrados nos dados de origem.

4. Considere bloquear as células na pasta de trabalho de referência para que não possam ser substituídas.

Como atualizar dados automaticamente

Ao trabalhar com vários arquivos do Excel, pode ser demorado atualizar os dados manualmente .

Se você deseja que sua fórmula VLOOKUP seja atualizada automaticamente quando você adiciona ou remove dados de outras pastas de trabalho, pode usar a função INDIRETO.

Veja como:

• Na fórmula VLOOKUP, substitua o argumento da matriz da tabela pela função INDIRETO.

• Na função INDIRETO, use uma referência à célula que contém a matriz da tabela na outra pasta de trabalho.

Para conseguir isso com nossas pastas de trabalho de amostra, siga estas etapas.

1. Use uma célula na coluna D para inserir a referência à planilha “Preços” da pasta de trabalho “Produto” e o intervalo de células A1:B5 assim: [Produto.xlsx]Preços!A:B

2. Na célula C2 da pasta de trabalho “Vendas”, use esta fórmula: =PROCV(A2, INDIRETO($D$1), 2, FALSO)

3. Copie a fórmula para baixo (as referências absolutas garantem que a célula com a planilha seja preservada).

Essas ações garantem que as alterações na planilha original sejam atualizadas automaticamente nas células conectadas.

Esta imagem mostra a função INDIRETO combinada com VLOOKUP:

Observe que este exemplo usa o intervalo “A:B” para referenciar as duas colunas. Isso significa que quando você adicionar mais produtos e preços, a fórmula continuará funcionando na pasta de trabalho atual.

Agora que você já experimentou o poder do VLOOKUP, vamos dar uma olhada em alguns usos avançados da função na seção!

Usos avançados da função VLOOKUP

Os exemplos neste artigo retornam uma única correspondência para cada valor de pesquisa. Se quiser trabalhar com várias correspondências de uma pasta de trabalho diferente, você pode usar uma fórmula de matriz.

Você também pode combinar VLOOKUP com outras funções. Por exemplo, você pode incorporá-lo em uma função SE para análises complexas adicionais.

A análise complexa inclui técnicas como testes qui-quadrado e testes T de amostras pareadas. Aqui está um exemplo de uso do teste qui-quadrado no Excel:

Usuários avançados aproveitam o VLOOKUP para comparar conjuntos de dados e identificar incompatibilidades. Você encontrará outras maneiras neste artigo para encontrar discrepâncias no Excel .

Mas e se você não quiser usar o VLOOKUP por qualquer motivo? Você está com sorte! Existem algumas alternativas, que abordaremos na próxima seção.

2 principais alternativas ao VLOOKUP

Quando se trata de procurar valores em planilhas diferentes, VLOOKUP costuma ser a função ideal. No entanto, existem algumas alternativas que podem ser úteis em determinadas situações.

Os dois mais comuns são:

• CORRESPONDÊNCIA DE ÍNDICE

• XLOOKUP

1. CORRESPONDÊNCIA DE ÍNDICE

INDEX e MATCH são uma poderosa combinação de duas funções que podem ser usadas para procurar valores em uma tabela. Ao contrário de VLOOKUP, que exige que o valor de pesquisa esteja na coluna mais à esquerda da tabela, INDEX MATCH pode pesquisar valores em qualquer coluna.

Veja como funciona:

1. A função INDEX retorna um valor de um intervalo especificado com base em um número de linha e coluna.

2. A função CORRESP retorna a posição de um valor de pesquisa em um intervalo especificado.

Para usar INDEX MATCH, você precisa especificar o intervalo que deseja pesquisar, a linha onde deseja encontrar o valor de pesquisa e a coluna onde deseja retornar o resultado.

Aqui está uma fórmula de exemplo:

=ÍNDICE(TableRange,MATCH(LookupValue,LookupRange,0),ColumnIndex)

Para usar isso com as planilhas de exemplo, esta é a fórmula:

=ÍNDICE([Produto.xlsx]Preços!$B$1:$B$5, CORRESP(A2, [Produto.xlsx]Preços!$A$1:$A$5, 0))

Isso se decompõe como:

• A função CORRESP procura o valor na célula A2 (nome do produto) dentro do intervalo A1:A5 na planilha “Preços” da pasta de trabalho “Produto”.

• A função ÍNDICE retorna um valor do intervalo B1:B5 na planilha “Preços” da pasta de trabalho “Produto”, com base no número da linha fornecido pela função CORRESP.

Esta imagem mostra a fórmula em ação:

2. XLOOKUP

XLOOKUP é uma função mais recente que foi introduzida no Excel 365 . É semelhante ao VLOOKUP, mas com alguns recursos adicionais.

Uma das maiores vantagens do XLOOKUP é que ele pode procurar valores à esquerda da coluna de pesquisa.

Veja como funciona:

1. A função XLOOKUP retorna um valor de um intervalo especificado com base em um valor de pesquisa.

2. Você pode especificar se deseja uma correspondência exata ou aproximada.

3. Você também pode especificar o que retornar se nenhuma correspondência for encontrada.

Para usar XLOOKUP, você precisa especificar o valor de pesquisa, o intervalo que deseja pesquisar e a coluna onde deseja retornar o resultado.

Aqui está uma fórmula de exemplo:

=XLOOKUP(Valor de Pesquisa,Intervalo de Pesquisa,Intervalo de Retorno,”Não Encontrado”,0)

Para usar isso com as planilhas de exemplo, esta é a fórmula:

=XLOOKUP(A2, [Produto.xlsx]Preços!$A$1:$A$5, [Produto.xlsx]Preços!$B$1:$B$5)

Isso se decompõe como:

• A2: O valor que você deseja pesquisar (nome do produto).

• [Produto.xlsx]Preços!$A$1:$A$5: O intervalo onde você deseja pesquisar o valor de pesquisa (nomes de produtos na planilha “Preços” da pasta de trabalho “Produto”).

• [Produto.xlsx]Preços!$B$1:$B$5: O intervalo que contém os valores de retorno (preços na planilha “Preços” da pasta de trabalho “Produto”).

Esta é a fórmula em ação:

Como usar o VLOOKUP em várias planilhas

Se você precisar executar um VLOOKUP entre duas pastas de trabalho, também poderá precisar usar o VLOOKUP em várias planilhas.

Felizmente, o processo para fazer isso é muito semelhante a executar um VLOOKUP entre duas pastas de trabalho.

Existem quatro etapas.

Etapa 1: identificar os componentes

Em vez de incluir a matriz da tabela como faria para uma planilha, você precisará incluir também o nome da planilha.

Por exemplo, se você deseja pesquisar um valor no intervalo A2:B6 na planilha Jan na pasta de trabalho Sales_reports.xlsx, sua fórmula ficaria assim:

=PROCV(A2,[Sales_reports.xlsx]Jan!$A$2:$B$6,2,FALSO)

Etapa 2: configurar seus dados

Certifique-se de que seus dados estejam organizados de forma a facilitar a referência em várias planilhas.

Você pode querer considerar a criação de guias diferentes para dados de referência específicos.

Etapa 3: use a função VLOOKUP

Depois de configurar seus dados, você pode usar a função VLOOKUP para pesquisar e recuperar dados de outras planilhas.

Apenas certifique-se de incluir o nome da planilha em sua fórmula, conforme mostrado acima.

(Opcional) Etapa 4: solucionar problemas de erros #N/A

Se você encontrar erros #N/A ao usar VLOOKUP em várias planilhas, verifique novamente sua fórmula para certificar-se de que referenciou corretamente todas as planilhas e intervalos necessários.

Você também pode verificar se está realizando uma correspondência exata, em vez de uma pesquisa de intervalo.

Pensamentos finais

Você aprendeu a usar o VLOOKUP para pesquisar e recuperar dados em duas planilhas. Essa função versátil facilita a pesquisa de valores em uma pasta de trabalho com base nos valores correspondentes em outra.

O poder da função VLOOKUP vai muito além de planilhas únicas, abrangendo diferentes pastas de trabalho para vincular e analisar dados.

Dominar essa função é um passo importante para se tornar um profissional do Excel, pois abre novas possibilidades de gerenciamento e interpretação dos seus dados.

Ao incorporar funções como INDIRETO, INDEX-MATCH ou XLOOKUP, você pode aumentar ainda mais a flexibilidade e eficácia de suas pesquisas de dados.

Continue praticando e explorando os vastos recursos do Excel; esta é apenas a ponta do iceberg. Boa caça aos dados!