Procure um único valor com VLOOKUP e HLOOKUP no Excel

As funções de pesquisa mais populares no Excel 2013 são as funções HLOOKUP (para pesquisa horizontal) e VLOOKUP (para pesquisa vertical). Essas funções estão localizadas no menu suspenso Pesquisa e Referência na guia Fórmulas da Faixa de Opções, bem como na categoria Pesquisa 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 na função VLOOKUP é o número da coluna cujos valores são comparados ao argumento lookup_value em uma tabela vertical. O argumento row_index_num na função HLOOKUP é o número da linha cujos valores são comparados a lookup_value em uma tabela horizontal.

Ao inserir os argumentos col_index_num ou row_index_num nas funções VLOOKUP e HLOOKUP, você deve inserir um valor maior que zero que não exceda 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.

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 argumento range_lookup opcional foi omitido da função.)

Observe que o exemplo da tabela de dicas 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:

= 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.