Como usar as funções VLOOKUP e HLOOKUP no Excel 2016

As funções de pesquisa mais populares do Excel 2016 são HLOOKUP (para pesquisa horizontal) e VLOOKUP (para pesquisa vertical). Essas funções estão localizadas no menu suspenso Consulta e Referência na guia Fórmulas da Faixa de Opções, bem como na categoria Consulta e Referência na caixa de diálogo Inserir Função. Eles fazem parte de um poderoso grupo de funções que podem retornar valores procurando-os em tabelas de dados.

A função VLOOKUP pesquisa verticalmente (de cima para baixo) a coluna mais à esquerda de uma tabela Lookup até que o programa localize um valor que corresponda ou exceda aquele que você está procurando. A função HLOOKUP pesquisa horizontalmente (da esquerda para a direita) a linha superior de uma tabela Lookup até localizar um valor que corresponda ou exceda aquele que você está procurando.

A função VLOOKUP usa a seguinte sintaxe:

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

A função HLOOKUP segue a sintaxe quase idêntica:

HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup])

Em ambas as funções, o argumento lookup_value é o valor que você deseja pesquisar na tabela Lookup, e table_array é o intervalo de células ou nome da tabela Lookup que contém o valor a ser pesquisado e o valor relacionado a retornar.

O argumento col_index_num designa a coluna da tabela de pesquisa que contém os valores que são retornados pela função VLOOKUP com base na correspondência do valor do argumento lookup_value com aqueles no argumento table_array. Você determina o argumento col_index_num contando quantas colunas esta coluna está à direita da primeira coluna da tabela de Consulta vertical e inclui a primeira coluna da tabela de Consulta nesta contagem.

O argumento row_index_num designa a linha que contém os valores retornados pela função HLOOKUP em uma tabela horizontal. Você determina o argumento row_index_num contando quantas linhas abaixo esta linha vem da linha superior da tabela de consulta horizontal. Novamente, você inclui a linha superior da tabela Lookup nesta contagem.

Ao inserir os argumentos col_index_num ou row_index_num nas funções VLOOKUP e HLOOKUP, o valor inserido não pode exceder o número total de colunas ou linhas na tabela Lookup.

O argumento opcional range_lookup nas funções VLOOKUP e HLOOKUP é o lógico VERDADEIRO ou FALSO que especifica se você deseja que o Excel encontre uma correspondência exata ou aproximada para o valor_procurado na matriz_tabela. Quando você especifica TRUE ou omite o argumento range_lookup na função VLOOKUP ou HLOOKUP, o Excel encontra uma correspondência aproximada. Quando você especifica FALSE como o argumento range_lookup , o Excel encontra apenas correspondências exatas.

Encontrar correspondências aproximadas pertence apenas quando você está procurando entradas numéricas (em vez de texto) na primeira coluna ou linha da tabela de pesquisa vertical ou horizontal. Quando o Excel não encontrar uma correspondência exata nesta coluna Lookup ou linha, ele localiza o segundo maior valor que não exceda o valor_procurado argumento e retorna o valor na coluna ou linha designada pelos núm_índice_coluna ou núm_índice_lin argumentos.

Ao usar as funções VLOOKUP e HLOOKUP, o texto ou as entradas numéricas na coluna ou linha de Pesquisa (ou seja, a coluna mais à esquerda de uma tabela de Pesquisa vertical ou a linha superior de uma tabela de Pesquisa horizontal) devem ser exclusivos. Essas entradas também devem ser organizadas ou classificadas em ordem crescente; ou seja, ordem alfabética para entradas de texto e ordem do menor para o maior para entradas numéricas.

A figura mostra um exemplo de uso da função PROCV para retornar uma gorjeta de 15% ou 20% de uma tabela de gorjetas, dependendo do total antes do imposto do cheque. A célula F3 contém a função VLOOKUP:

= VLOOKUP (Pretax_Total, Tip_Table, IF (Tip_Percentage = 0,15,2,3)) 

Esta fórmula retorna o valor da gorjeta com base na porcentagem da gorjeta na célula F1 e o valor antes dos impostos do cheque na célula F2.

Usando a função PROCV para retornar a quantidade da dica a ser adicionada de uma tabela de Consulta.

Para usar esta tabela de gorjetas, insira a porcentagem da gorjeta (15% ou 20%) na célula F1 (denominada Tip_Percentage) e o valor do cheque antes do imposto na célula F2 (denominada Pretax_Total). O Excel então pesquisa o valor que você insere na célula Pretax_Total na primeira coluna da tabela Lookup, que inclui o intervalo de células A2: C101 e é denominado Tip_Table.

O Excel então move para baixo os valores na primeira coluna de Tip_Table até encontrar uma correspondência, após o que o programa usa o argumento col_index_num na função VLOOKUP para determinar qual valor de gorjeta daquela linha da tabela retornar para a célula F3. Se o Excel descobrir que o valor inserido na célula Pretax_Total ($ 16,50 neste exemplo) não corresponde exatamente a um dos valores na primeira coluna de Tip_Table, o programa continua a pesquisar o intervalo de comparação até encontrar o primeiro valor que excede o total antes dos impostos (17,00 na célula A19 neste exemplo). O Excel então volta para a linha anterior na tabela e retorna o valor na coluna que corresponde ao argumento col_index_num da função PROCV. (Isso ocorre porque o range_lookup opcionalargumento foi omitido da função.)

Observe que o exemplo da tabela de dicas na figura usa uma função IF para determinar o argumento col_index_num para a função VLOOKUP na célula F3. A função IF determina o número da coluna a ser usado na tabela de gorjetas, combinando a porcentagem inserida em Tip_Percentage (célula F1) com 0,15. Se eles corresponderem, a função retornará 2 como o argumento col_index_num e a função VLOOKUP retornará um valor da segunda coluna (15% coluna B) no intervalo Tip_Table. Caso contrário, a função IF retorna 3 como o argumento col_index_num e a função VLOOKUP retorna um valor da terceira coluna (a coluna C de 20%) no intervalo Tip_Table.

A figura a seguir mostra um exemplo que usa a função HLOOKUP para pesquisar o preço de cada item de padaria armazenado em uma tabela de Consulta de preço separada e, em seguida, retornar esse preço para a coluna Preço / Doz da lista de Vendas Diárias. A célula F3 contém a fórmula original com a função HLOOKUP que é então copiada para a coluna F:

Usando a função HLOOKUP para retornar o preço de um item de padaria de uma tabela de Consulta.

= HLOOKUP (item, tabela_de_preço, 2, FALSO)

Nesta função HLOOKUP, o nome do intervalo Item que é dado à coluna Item no intervalo C3: C62 é definido como o argumento lookup_value e o nome do intervalo de células Tabela de preços que é dado ao intervalo de células I1: M2 é o argumento table_array . O argumento row_index_num é 2 porque você deseja que o Excel retorne os preços na segunda linha da tabela de Consulta de Preços, e o argumento range_lookup opcional é FALSO porque o nome do item na lista de Vendas Diárias deve corresponder exatamente ao nome do item na tabela de Consulta de Preços .

Ao fazer com que a função HLOOKUP use o intervalo da tabela de preços para inserir o preço por dúzia para cada item de panificação na lista de vendas diárias, você torna muito simples atualizar qualquer uma das vendas na lista. Tudo que você precisa fazer é alterar seu preço / custo Doz nesta faixa, e a função HLOOKUP atualiza imediatamente o novo preço na lista de Vendas Diárias onde quer que o item seja vendido.