A função VLOOKUP do Excel é usada para localizar e retornar informações de uma tabela de dados com base em um valor de pesquisa escolhido.
Normalmente, o VLOOKUP requer que o valor de pesquisa esteja na coluna mais à esquerda da tabela de dados, e a função retorna outro campo de dados localizado na mesma linha à direita desse valor.
No entanto, combinando VLOOKUP com a função ESCOLHER, uma fórmula de pesquisa à esquerda pode ser criada que:
- Permite que o valor de pesquisa seja de qualquer coluna na tabela de dados
- Retorna informações localizadas em qualquer coluna à esquerda do valor de pesquisa
Nota: Estas instruções aplicam-se às versões do Excel 2019, 2016, 2013, 2010 e Excel para o Office 365.
01 de 05Tutorial: Usando as funções VLOOKUP e CHOOSE em uma fórmula de pesquisa à esquerda
Para criar a fórmula de pesquisa à esquerda vista na imagem de exemplo, use a fórmula:
= VLOOKUP ($ D $ 2, ESCOLHA ({1,2}, $ F: $ F, $ D: $ D), 2, FALSE)
Neste exemplo, a fórmula possibilita encontrar a peça fornecida pelas diferentes empresas listadas na coluna 3 da tabela de dados.
O trabalho da função ESCOLHER na fórmula é fazer com que o VLOOKUP acredite que a coluna 3 é a coluna 1. Como resultado, o nome da empresa pode ser usado como o valor de pesquisa para encontrar o nome da peça fornecida por cada empresa.
Digite os dados do tutorial
- Digite o cabeçalho Fornecedor na célula D1.
- Digite o cabeçalho Parte na cela E1.
- Digite a tabela de dados vista na imagem acima nas células D4 a F9
- Linhas 2 e 3 são deixadas em branco para acomodar os critérios de pesquisa e a fórmula de pesquisa à esquerda criada durante este tutorial
Abra a caixa de diálogo VLOOKUP
Embora seja possível digitar a fórmula diretamente na célula F1 na planilha, muitas pessoas têm dificuldade com a sintaxe da fórmula.
Nesse caso, é mais seguro usar a caixa de diálogo VLOOKUP. Quase todas as funções do Excel têm uma caixa de diálogo que permite inserir cada um dos argumentos da função em uma linha separada.
- Clique na célula E2 da planilha. E2 é o local onde os resultados da fórmula de pesquisa esquerda serão exibidos.
- Clique no Fórmulas guia da faixa de opções.
- Clique no Pesquisa e Referência opção na faixa de opções para abrir a lista suspensa de funções.
- Clique em VLOOKUP na lista para abrir a caixa de diálogo da função.
Inserindo Argumentos na Caixa de Diálogo VLOOKUP
Os argumentos de uma função são os valores usados pela função para calcular um resultado.
Na caixa de diálogo de uma função, o nome de cada argumento está em uma linha separada seguida por um campo no qual inserir um valor.
Insira os seguintes valores para cada um dos argumentos do VLOOKUP na linha correta da caixa de diálogo, conforme mostrado na imagem anexa.
O valor da pesquisa
O valor de pesquisa é o campo de informações usado para pesquisar a matriz da tabela. VLOOKUP retorna outro campo de dados da mesma linha que o valor de pesquisa.
Este exemplo usa uma referência de célula para o local em que o nome da empresa será inserido na planilha. A vantagem disso é que facilita a alteração do nome da empresa sem editar a fórmula.
- Clique no lookup_value linha na caixa de diálogo.
- Clique na célula D2 para adicionar esta referência de célula ao lookup_value linha.
- aperte o F4 tecla no teclado para tornar a referência da célula absoluta - $ D $ 2.
Referências De Células Absolutas
Referências de célula absoluta são usadas para o valor de pesquisa e argumentos de matriz de tabela para evitar erros se a fórmula de pesquisa é copiada para outras células na planilha.
Entrando na função CHOOSE
O argumento da matriz da tabela é o bloco de dados contíguos dos quais informações específicas são recuperadas.
Normalmente, o VLOOKUP só olha para a direita do argumento do valor de pesquisa para encontrar dados na matriz da tabela. Para fazê-lo olhar para a esquerda, o VLOOKUP deve ser enganado reorganizando as colunas na matriz da tabela usando a função CHOOSE.
Nesta fórmula, a função CHOOSE realiza duas tarefas:
- Cria um array de tabelas com apenas duas colunas de largura (colunas D e F).
- Ele altera a ordem da direita para a esquerda das colunas na matriz da tabela, de modo que a coluna F seja a primeira ea coluna D a segunda.
Inserir funções
Ao inserir funções manualmente, cada um dos argumentos da função deve ser separado por uma vírgula.
- Na caixa de diálogo da função VLOOKUP, clique no Table_array linha.
- Digite o seguinte ESCOLHER função:ESCOLHER ({1,2}, $ F: $ F, $ D: $ D)
Número do índice da coluna
Normalmente, o número do índice da coluna indica qual coluna da matriz da tabela contém os dados que você está procurando. No entanto, nesta fórmula, refere-se à ordem das colunas definidas pela função CHOOSE.
A função CHOOSE cria uma matriz de tabela com duas colunas de largura com a coluna F primeiro seguida pela coluna D. Como as informações procuradas - o nome da peça - estão na coluna D, o valor do argumento de índice da coluna deve ser definido como 2.
- Clique no Col_index_num linha na caixa de diálogo.
- Digite um 2 nesta linha.
Pesquisa de alcance
O argumento Range_lookup do VLOOKUP é um valor lógico (TRUE ou FALSE apenas) que indica se você deseja que o VLOOKUP encontre uma correspondência exata ou aproximada ao valor de pesquisa.
- Se TRUE ou se este argumento for omitido, o VLOOKUP retornará uma correspondência exata para o Lookup_value ou, se uma correspondência exata não for encontrada, o VLOOKUP retornará o próximo valor maior. Para a fórmula para fazer isso, os dados na primeira coluna de Table_array devem ser classificados em ordem crescente.
- Se FALSE, o VLOOKUP usa apenas 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, será retornado um erro # N / A.
Neste tutorial, uma vez que estamos procurando por um nome de peça específico, o Range_lookup é definido como Falso de modo que apenas correspondências exatas sejam retornadas pela fórmula.
- Clique no Pesquisa de alcance linha na caixa de diálogo.
- Digite a palavra Falso nesta linha para indicar que queremos que o VLOOKUP retorne uma correspondência exata para os dados que estamos procurando.
- Clique Está bem para concluir a fórmula de pesquisa à esquerda e fechar a caixa de diálogo.
- Como ainda não inserimos o nome da empresa na célula D2, um erro # N / A aparece na célula E2.
Retornando dados com a fórmula de pesquisa à esquerda
Para descobrir quais empresas fornecem quais partes, digite o nome de uma empresa na célula D2 e pressione o ENTRAR tecla no teclado.
O nome da peça é exibido na célula E2.
- Clique na célula D2 na sua planilha.
- Tipo Gadgets Plus na célula D2 e pressione o ENTRAR tecla no teclado.
- O texto "Gadgets" - a parte fornecida pela empresa Gadgets Plus - deve ser exibido na célula E2.
Teste a fórmula de pesquisa ainda mais, digitando outros nomes de empresas na célula D2 e o nome da parte correspondente deve aparecer na célula E2.
Se uma mensagem de erro, como # N / A, aparecer na célula E2, verifique se há erros de ortografia na célula D2.
04 de 05Criando uma matriz de tabela de duas colunas
A sintaxe da função CHOOSE é:
= ESCOLHER (Index_number, Value1, Value2, … Value254)
A função CHOOSE normalmente retorna um valor da lista de valores (Value1 para Value254) com base no número de índice inserido.
Se o número do índice for 1, a função retornará o Valor1 da lista; se o número do índice for 2, a função retornará o Valor2 da lista e assim por diante.
Quando vários números de índice são inseridos, a função retorna vários valores em qualquer ordem desejada. Obter CHOOSE para retornar vários valores é feito criando uma matriz.
Para inserir uma matriz, coloque os números digitados com chaves ou colchetes. Dois números são inseridos para o número do índice:{ 1,2 }.
Deve-se notar que CHOOSE não se limita a criar uma tabela de duas colunas. Incluindo um número adicional na matriz - como {1,2,3} - e um intervalo adicional no argumento de valor, CHOOSE cria uma tabela de três colunas.
Colunas adicionais permitem que você retorne informações diferentes com a fórmula de pesquisa esquerda alterando o argumento do número de índice da coluna de VLOOKUP para o número da coluna que contém as informações desejadas.
05 de 05Alterando a ordem das colunas com a função CHOOSE
Na função CHOOSE usada nesta fórmula:
ESCOLHER ({1,2}, $ F: $ F, $ D: $ D)
o intervalo da coluna F é listado antes da coluna D.
Uma vez que a função CHOOSE configura o array de tabelas do VLOOKUP (a fonte de dados para essa função), a comutação da ordem das colunas na função CHOOSE é passada para o VLOOKUP.
No que diz respeito ao VLOOKUP, o array da tabela tem apenas duas colunas de largura, com a coluna F à esquerda e a coluna D à direita. Como a coluna F contém o nome da empresa que queremos procurar e, como a coluna D contém os nomes das partes, o VLOOKUP pode executar suas tarefas de pesquisa regulares na localização de dados que estão localizados à esquerda do valor da pesquisa.
Como resultado, o VLOOKUP pode usar o nome da empresa para encontrar a peça que eles fornecem.
