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. Saiba como criar uma fórmula de pesquisa que retorne 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.
Linha de fundo
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 da primeira função.
Insira os dados do tutorial
Neste tutorial, a função COLUMN é inserida como o argumento do número do índice da coluna para PROCV. A última etapa do tutorial envolve copiar a fórmula de pesquisa para colunas adicionais para recuperar valores adicionais para a parte escolhida.
O primeiro passo 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.
- Digite o segundo intervalo nas células D4 a G10.
Os critérios de pesquisa e a fórmula de pesquisa criados 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 de intervalo 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 para os dados (como mostrado na linha 4), apenas os dados.
-
Destaque células D5 a G10 na planilha.
Image -
Coloque o cursor na caixa de nome localizada acima da coluna A, digite Table e pressione Enter. As células D5 a G10 têm o nome de intervalo Table.
Image - 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 em 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 PROCV. Quase todas as funções do Excel têm 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.
Image -
Na faixa de opções, vá para a guia Formulas e selecione Lookup & Reference.
Image -
Selecione VLOOKUP para abrir a caixa de diálogo Function Arguments.
Image - A caixa de diálogo Argumentos da Função é onde os parâmetros da função PROCV são inseridos.
Digite o Argumento do Valor de Pesquisa
Normalmente, o valor de pesquisa corresponde a um campo de dados na primeira coluna da tabela de dados. Neste exemplo, o valor de pesquisa refere-se ao nome da peça sobre a qual você deseja localizar informações. Os tipos de dados permitidos para o valor de pesquisa são dados de texto, valores lógicos, números e referências de células.
Referências de células 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 o valor de pesquisa, altera e cria erros nas células F2 e G2.
Referências de células absolutas não mudam quando as fórmulas são copiadas.
Para evitar 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 da célula, como $D$2.
-
Na caixa de diálogo Argumentos da Função, coloque o cursor na caixa de texto lookup_value. Em seguida, na planilha, selecione cell D2 para adicionar esta referência de célula ao lookup_value. A célula D2 é onde o nome da peça será inserido.
Image -
Sem mover o ponto de inserção, pressione a tecla F4 para converter D2 para a referência de célula absoluta $D$2.
Image - Deixe a caixa de diálogo da função PROCV aberta para a próxima etapa do tutorial.
Insira 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 desejadas. 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 especificadas.
O argumento da matriz da tabela deve ser inserido como um intervalo contendo as referências de célula para a tabela de dados ou como um nome de intervalo.
Para adicionar a tabela de dados à função VLOOKUP, coloque o cursor na caixa de texto table_array na caixa de diálogo e digite Tablepara inserir o nome do intervalo para este argumento.
Aninha a função COLUMN
Normalmente, PROCV só retorna dados de uma coluna de uma tabela de dados. Esta coluna é definida pelo argumento do número de índice da coluna. Neste exemplo, no entanto, 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 argumento de referência. 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.
-
Na caixa de diálogo Function Arguments, coloque o cursor na caixa de texto Col_index_num e digite COLUMN(. (Certifique-se de incluir o colchete aberto.)
Image -
Na planilha, selecione cell B1 para inserir essa referência de célula como o argumento Reference.
Image - Digite um colchete de fechamento para completar a função COLUMN.
Insira o Argumento de Pesquisa do Intervalo PROCV
O argumento Range_lookup do VLOOKUP é um valor lógico (VERDADEIRO ou FALSO) que indica se o VLOOKUP deve encontrar uma correspondência exata ou aproximada para o Lookup_value.
- TRUE ou Omitido: VLOOKUP retorna uma correspondência próxima para o Lookup_value. Se uma correspondência exata não for encontrada, PROCV retornará o próximo maior valor. Os dados na primeira coluna de Table_array devem ser classificados em ordem crescente.
- FALSE: VLOOKUP usa uma correspondência exata para o 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, então 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 False para dizer a VLOOKUP 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. Ele 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 de uma só vez. Para fazer isso, a fórmula de pesquisa deve residir em todos os campos dos quais você deseja obter 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 o Lookup_value.
Como os dados estão dispostos em um padrão regular na planilha, copie a fórmula de pesquisa na célula E2 para células F2 e G2 À medida que a fórmula é copiada, o Excel atualiza a referência de célula relativa na função COLUMN (célula B1) para refletir a nova localização da fórmula. O Excel não altera a referência de célula absoluta (como $D$2) e o intervalo nomeado (Tabela) à medida que a fórmula é copiada.
Há mais de uma maneira de copiar dados no Excel, mas a maneira mais fácil é usar o identificador de preenchimento.
-
Selecione célula E2, onde a fórmula de pesquisa está localizada, para torná-la a célula ativa.
Image -
Arraste a alça de preenchimento até a cell G2. As células F2 e G2 exibem o erro N/A que está presente na célula E2.
Image -
Para usar as fórmulas de pesquisa para recuperar informações da tabela de dados, na planilha selecione célula D2, digite Widget e pressione Enter.
Image 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 de 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 de outras partes na célula D2 e observe os resultados nas células E2 a G2.
Image - Cada célula que contém a fórmula de pesquisa contém um dado diferente 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.
