Encontre rapidamente vários campos de dados com a função VLOOKUP do Excel

Neste artigo

Ao combinar a função VLOOKUP do Excel com a função COLUMN, você pode criar uma fórmula de pesquisa que retorna vários valores de uma única linha de um banco de dados ou tabela de dados. Aprenda a criar uma fórmula de pesquisa que retorna vários valores de um único registro de dados. As instruções neste artigo se aplicam ao Excel 2019, 2016, 2013, 2010; e Excel para Microsoft 365.

Retornar vários valores com VLOOKUP do Excel

A fórmula de pesquisa requer que a função COLUMN seja aninhada dentro de VLOOKUP. Aninhar uma função envolve inserir a segunda função como um dos argumentos para a primeira função.

Insira os dados do tutorial

Neste tutorial, a função COLUMN é inserida como o índice da coluna argumento de número para VLOOKUP. A última etapa do tutorial envolve copiar a fórmula de pesquisa para colunas adicionais para recuperar valores adicionais para a parte escolhida. A primeira etapa neste tutorial é inserir os dados em uma planilha do Excel. Para seguir as etapas deste tutorial, insira os dados mostrados na imagem abaixo nas seguintes células:

Insira o intervalo superior de dados nas células D1 a G1.
Insira o segundo intervalo nas células D4 a G10.

Os critérios de pesquisa e a fórmula de pesquisa criada neste tutorial são inseridos na linha 2 da planilha. Este tutorial não inclui a formatação básica do Excel mostrada na imagem, mas isso não afeta o funcionamento da fórmula de pesquisa.

Crie um intervalo nomeado para a tabela de dados

Um intervalo nomeado é uma maneira fácil de se referir a um intervalo de dados em uma fórmula. Em vez de digitar as referências de célula para dados, digite o nome do intervalo. Uma segunda vantagem de usar um intervalo nomeado é que as referências de célula para esse intervalo nunca mudam, mesmo quando a fórmula é copiada para outras células na planilha. Os nomes dos intervalos são uma alternativa ao uso de referências de células absolutas para evitar erros ao copiar fórmulas. O nome do intervalo não inclui os títulos ou nomes de campo dos dados (conforme mostrado na linha 4), apenas os dados.

Realçar células D5 para G10 na planilha.
Coloque o cursor na caixa de nome localizada acima da coluna A, digite Mesa, então aperte Entrar. As células D5 a G10 têm o nome de intervalo de Tabela.
O nome do intervalo para o argumento da matriz da tabela VLOOKUP é usado posteriormente neste tutorial.

Abra a caixa de diálogo PROCV

Embora seja possível digitar a fórmula de pesquisa diretamente em uma célula de uma planilha, muitas pessoas acham difícil manter a sintaxe correta – especialmente para uma fórmula complexa como a usada neste tutorial. Como alternativa, use a caixa de diálogo Argumentos da função VLOOKUP. Quase todas as funções do Excel possuem uma caixa de diálogo onde cada um dos argumentos da função é inserido em uma linha separada.

Selecione célula E2 da planilha. Este é o local onde os resultados da fórmula de pesquisa bidimensional serão exibidos.
Na faixa de opções, vá para o Fórmulas guia e selecionar Pesquisa e Referência.
Selecione PROCV para abrir o Argumentos de função caixa de diálogo.
A caixa de diálogo Argumentos da função é onde os parâmetros da função VLOOKUP são inseridos.

Insira o argumento do valor de pesquisa

Normalmente, a pesquisa valor corresponde a um campo de dados na primeira coluna da tabela de dados. Neste exemplo, o lookup valor refere-se ao nome da parte sobre a qual deseja encontrar informações. Os tipos de dados permitidos para a pesquisa valor são dados de texto, valores lógicos, números e referências de células.

Referências celulares absolutas

Quando as fórmulas são copiadas no Excel, as referências das células mudam para refletir o novo local. Se isso acontecer, D2, a referência de célula para a pesquisa valor, altera e cria erros nas células F2 e G2. As referências de células absolutas não mudam quando as fórmulas são copiadas. Para evitar os erros, converta a referência de célula D2 em uma referência de célula absoluta. Para criar uma referência de célula absoluta, pressione a tecla F4. Isso adiciona cifrões ao redor da referência de célula, como $ D $ 2.

Na caixa de diálogo Argumentos da função, coloque o cursor no lookup_value caixa de texto. Então, na planilha, selecione célula D2 para adicionar esta referência de célula ao lookup_value. A célula D2 é onde o nome da peça será inserido.
Sem mover o ponto de inserção, pressione o F4 para converter D2 para a referência de célula absoluta $ D $ 2.
Deixe a caixa de diálogo da função PROCV aberta para a próxima etapa do tutorial.

Digite o argumento da matriz da tabela

Uma matriz de tabela é a tabela de dados que a fórmula de pesquisa pesquisa para encontrar as informações que você deseja. A matriz da tabela deve conter pelo menos duas colunas de dados. A primeira coluna contém o argumento do valor de pesquisa (que foi configurado na seção anterior), enquanto a segunda coluna é pesquisada pela fórmula de pesquisa para encontrar as informações que você especificar. O argumento da matriz da tabela deve ser inserido como um intervalo contendo as referências de células para a tabela de dados ou como um nome de intervalo. Para adicionar a tabela de dados à função VLOOKUP, coloque o cursor no table_array caixa de texto na caixa de diálogo e digite Mesa para inserir o nome do intervalo para este argumento.

Aninhar a função COLUMN

Normalmente, VLOOKUP retorna apenas dados de uma coluna de uma tabela de dados. Esta coluna é definida pelo argumento do número do índice da coluna. Neste exemplo, entretanto, há três colunas e o número do índice da coluna precisa ser alterado sem editar a fórmula de pesquisa. Para fazer isso, aninhe a função COLUMN dentro da função VLOOKUP como o argumento Col_index_num. Ao aninhar funções, o Excel não abre a caixa de diálogo da segunda função para inserir seus argumentos. A função COLUMN deve ser inserida manualmente. A função COLUMN tem apenas um argumento, o argumento Reference, que é uma referência de célula. A função COLUMN retorna o número da coluna fornecida como o argumento Reference. Ele converte a letra da coluna em um número. Para encontrar o preço de um item, use os dados da coluna 2 da tabela de dados. Este exemplo usa a coluna B como referência para inserir 2 no argumento Col_index_num.

No Argumentos de função caixa de diálogo, coloque o cursor no Col_index_num caixa de texto e tipo COLUNA(. (Certifique-se de incluir o colchete redondo aberto.)
Na planilha, selecione célula B1 para inserir essa referência de célula como o argumento de referência.
Digite um fechamento do colchete para completar a função COLUNA.

Digite o argumento de pesquisa de intervalo VLOOKUP

O argumento Range_lookup de PROCV é um valor lógico (VERDADEIRO ou FALSO) que indica se PROCV deve encontrar uma correspondência exata ou aproximada para o valor_procurado.

VERDADEIRO ou Omitido: VLOOKUP retorna uma correspondência próxima ao valor_procurado. Se uma correspondência exata não for encontrada, VLOOKUP retorna o próximo maior valor. Os dados na primeira coluna de Table_array devem ser classificados em ordem crescente.
FALSO: VLOOKUP usa uma correspondência exata para Lookup_value. Se houver dois ou mais valores na primeira coluna de Table_array que correspondam ao valor de pesquisa, o primeiro valor encontrado será usado. Se uma correspondência exata não for encontrada, um erro # N / A será retornado.

Neste tutorial, informações específicas sobre um determinado item de hardware serão pesquisadas, portanto, Range_lookup é definido como FALSE. Na caixa de diálogo Argumentos da Função, coloque o cursor na caixa de texto Range_lookup e digite Falso para dizer a PROCV para retornar uma correspondência exata para os dados.

Selecione OK para completar a fórmula de pesquisa e fechar a caixa de diálogo. A célula E2 conterá um erro # N / A porque os critérios de pesquisa não foram inseridos na célula D2. Este erro é temporário. Isso será corrigido quando os critérios de pesquisa forem adicionados na última etapa deste tutorial.

Copie a fórmula de pesquisa e insira os critérios

A fórmula de pesquisa recupera dados de várias colunas da tabela de dados ao mesmo tempo. Para fazer isso, a fórmula de pesquisa deve residir em todos os campos dos quais você deseja informações. Para recuperar dados das colunas 2, 3 e 4 da tabela de dados (o preço, o número da peça e o nome do fornecedor), insira um nome parcial como valor_procurado. Uma vez que os dados são dispostos em um padrão regular na planilha, copie a fórmula de pesquisa em célula E2 para células F2 e G2. Conforme a fórmula é copiada, o Excel atualiza a referência de célula relativa na função COLUNA (célula B1) para refletir o novo local da fórmula. O Excel não altera a referência absoluta da célula (como $ D $ 2) e o intervalo nomeado (Tabela) conforme a fórmula é copiada. Há mais de uma maneira de copiar dados no Excel, mas a maneira mais fácil é usar o indicador de preenchimento.

Selecione célula E2, onde a fórmula de pesquisa está localizada, para torná-la a célula ativa.
Arraste a alça de preenchimento até célula G2. As células F2 e G2 exibem o erro # N / A que está presente na célula E2.
Para usar as fórmulas de pesquisa para recuperar informações da tabela de dados, na planilha selecione célula D2, modelo Ferramentae pressione Entrar.

As seguintes informações são exibidas nas células E2 a G2.
- E2: $ 14,76 – o preço de um widget
- F2: PN-98769 – o número da peça de um widget
- G2: Widgets Inc. – o nome do fornecedor de widgets
Para testar a fórmula de matriz PROCV, digite o nome das outras partes na célula D2 e observe os resultados nas células E2 a G2.
Cada célula que contém a fórmula de pesquisa contém dados diferentes sobre o item de hardware que você pesquisou.

A função VLOOKUP com funções aninhadas como COLUMN fornece um método poderoso para pesquisar dados dentro de uma tabela, usando outros dados como referência de pesquisa.