Quando estiver usando o Excel pela primeira vez, você provavelmente encontrará algumas funções que são fundamentais para fazer referência e pesquisar dados. Na maioria dos casos, você usará as funções de pesquisa do Excel.

Neste artigo, abordaremos as várias funções relacionadas à busca de informações no Excel e explicaremos como você pode usá-las por meio de exemplos. Se você deseja aprimorar suas habilidades em Excel em todas as áreas, não deixe de conferir nosso programa de habilidades em Excel Fundamentals.

O que são funções de pesquisa do Excel?

As funções de pesquisa no Excel permitem que os usuários pesquisem dados específicos em um conjunto de dados e retornem as informações correspondentes de outra coluna ou linha. Eles são essenciais na análise de dados para fazer referência, cruzar referências e extrair percepções significativas de conjuntos de dados grandes ou complexos. conjuntos de dados.

Por que as funções de pesquisa são importantes? 

As funções de pesquisa são ótimas para a recuperação eficiente de dados. A recuperação eficiente de dados é fundamental para a produtividade, especialmente quando você lida com planilhas grandes ou automatiza fluxos de trabalho de relatórios. As funções de pesquisa reduzem a busca manual, garantem a consistência e permitem soluções de dados dimensionáveis.

Por exemplo, imagine que você tenha uma tabela de vendas com milhares de linhas e colunas e precise encontrar o total de vendas de um produto ou região específica. 

Sem as funções de pesquisa, você precisaria pesquisar manualmente em cada linha para encontrar as informações relevantes, o que consome muito tempo e está sujeito a erros humanos.

Saiba mais sobre essas funções em nosso Curso de preparação de dados no Excel.

Tipos de funções de pesquisa no Excel

O Excel oferece várias funções de pesquisa, cada uma com pontos fortes exclusivos, adaptados a diferentes casos de uso. Compreender as diferenças entre essas funções pode melhorar significativamente sua capacidade de gerenciar e analisar dados com eficiência.

Vamos examinar alguns tipos comuns de funções abaixo.

1. LOOKUP

Fonte: Microsoft

A função de pesquisa original é a mais básica e funciona com matrizes ordenadas. Ele procura um valor em uma única linha ou coluna e retorna um valor da mesma posição em outra linha ou coluna. Ele suporta apenas correspondências aproximadas.

2. VLOOKUP (Vertical Lookup)

Fonte: Microsoft

VLOOKUP é uma das funções mais usadas nas pastas de trabalho herdadas do Excel. Ele procura um valor na primeira coluna de uma tabela e retorna um valor na mesma linha a partir de uma coluna especificada. 

Ele oferece suporte à correspondência exata e aproximada, mas tem limitações, como a incapacidade de pesquisar à esquerda e problemas de desempenho com grandes conjuntos de dados.

3. HLOOKUP (pesquisa horizontal)

Fonte: Tutorial do HLOOKUP

HLOOKUP funciona de forma semelhante ao VLOOKUP, mas procura um valor na primeira linha de uma tabela e retorna um valor de uma linha especificada. É útil quando seus dados são organizados horizontalmente em vez de verticalmente.

Você deve considerar o uso do HLOOKUP em vez do VLOOKUP quando seus dados estiverem organizados em linhas em vez de colunas.

4. INDEX e MATCH

O INDEX e MATCH apresenta o uso de uma combinação poderosa de duas funções:

• INDEX retorna o valor de uma célula com base nos números de linha e coluna.
• MATCH retorna a posição de um valor em uma linha ou coluna. Usados juntos, eles oferecem mais flexibilidade do que o VLOOKUP e são preferidos em cenários complexos, pois suportam pesquisas da esquerda para a direita e da direita para a esquerda e não exigem dados classificados.

5. XLOOKUP

Introduzido no Excel 2019 e no Microsoft 365, XLOOKUP é o substituto moderno do VLOOKUP e do HLOOKUP. Ele permite a correspondência exata ou aproximada, funciona horizontal ou verticalmente e oferece suporte ao tratamento de erros, o que o torna a função de pesquisa mais versátil até o momento.

A função LOOKUP

Vamos começar explorando a função LOOKUP no Excel, explorando a sintaxe e os parâmetros, a forma vetorial e um exemplo prático. 

Sintaxe e parâmetros

=LOOKUP(lookup_value, lookup_vector, [result_vector])

Aqui estão algumas explicações:

• lookup_value: O valor a ser pesquisado.
• lookup_vector: A única linha ou coluna a ser pesquisada.
• result_vector (opcional): O intervalo que contém o valor a ser retornado.

Usando a forma vetorial

A forma vetorial é comumente usada e exige que os campos lookup_vector e result_vector tenham o mesmo tamanho.

Explicação com exemplos

=LOOKUP(90, A2:A10, B2:B10)

Essa função procura o maior valor menor ou igual a 90 em A2:A10 e, em seguida, retorna o valor correspondente de B2:B10.

Nesse caso, somente a linha 5 corresponderia aos parâmetros que definimos. Portanto, a saída da função produziria o resultado "Médio-Alto".

Limitações da função LOOKUP

• Requisito para dados classificados: O lookup_vector deve ser classificado em ordem crescente.
• Comportamento de correspondência aproximado: Não oferece suporte a correspondências exatas - retorna o valor mais próximo menor ou igual a lookup_value.

A função VLOOKUP

Nex é a função VLOOKUP, que é uma das funções mais comumente usadas em versões legadas do Excel. 

Sintaxe e parâmetros

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

• lookup_value: O valor a ser pesquisado na primeira coluna do table_array.
• table_array: O intervalo da tabela que você deseja pesquisar.
• col_index_num: O número da coluna a partir da qual você retornará um valor.
• range_lookup: Opcional; TRUE para correspondência aproximada, FALSE para exata.

Como usar o VLOOKUP: Guia passo a passo com exemplos

Agora, vamos explicar rapidamente como essa função funciona usando um exemplo.

Usaremos a seguinte função em um conjunto de dados simples.

=VLOOKUP("John", A2:C10, 3, FALSE)

Essa função procurará quaisquer entradas que pertençam ao João e emitirá o valor do salário dele.

Procura por "John" na coluna A e retorna o valor da terceira coluna (coluna C) na mesma linha.

Como esperado, o resultado da função VLOOKUP é 6000, que corresponde à linha 6, que é o valor que estávamos procurando, já que pertence a John.

Correspondências exatas versus aproximadas

As correspondências exatas são o tipo de correspondência mais comumente usado no VLOOKUP. Isso significa que a função só retornará um resultado se encontrar uma correspondência exata para o valor de pesquisa na primeira coluna do intervalo especificado.

Por exemplo, se você usasse uma correspondência exata ao pesquisar o salário de João, a função VLOOKUP só retornaria um resultado se houvesse uma linha com "João" na primeira coluna e seu salário na segunda coluna.

Por outro lado, as correspondências aproximadas permitem alguma flexibilidade na busca de valores. Isso é útil quando você trabalha com dados numéricos ou dados que podem ter pequenas variações (por exemplo, devido a arredondamento ou formatação).

Para resumir:

• Exato (FALSO): Encontra uma correspondência precisa.
• Aproximado (VERDADEIRO): Requer dados classificados e encontra a correspondência mais próxima.

Erros comuns e solução de problemas

Quando você trabalha com o VLOOKUP pela primeira vez, podem ocorrer alguns erros comuns. 

Manipulação de erros #N/A

Um dos erros mais comuns ao usar o VLOOKUP é #N/A, que significa "Not Available" (não disponível). Isso geralmente ocorre quando o valor que está sendo pesquisado não pode ser encontrado no intervalo especificado.

Isso também pode ser causado por um lookup_value ausente ou por dados não classificados ao usar uma correspondência aproximada.

Para solucionar esse erro, verifique novamente se o valor da pesquisa está escrito corretamente e se existe na primeira coluna do intervalo em que você está pesquisando. Além disso, certifique-se de que seus dados estejam classificados em ordem crescente se você estiver usando uma correspondência aproximada.

Outro problema em potencial é uma incompatibilidade entre os tipos de dados. Por exemplo, se uma coluna contiver números representados como texto e outra coluna contiver números reais, o VLOOKUP poderá não funcionar corretamente.

Problemas com o número de índice da coluna

Outro erro que pode ocorrer é o #REF! Erro. Se col_index_num exceder o número de colunas em table_array, ocorrerá um erro #REF!

A função HLOOKUP

Para que você procure um valor na linha superior de uma tabela e retorne um valor de uma linha especificada na mesma coluna, a função HLOOKUP costuma ser a melhor opção. Vamos ver como isso funciona.

Sintaxe e parâmetros

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

• lookup_value: O valor a ser pesquisado na primeira linha.
• table_array: A tabela que você deseja pesquisar.
• row_index_num: O número da linha a partir da qual você retornará um valor.
• range_lookup: TRUE (verdadeiro) (aproximado) ou FALSE (falso) (exato).

Como usar o HLOOKUP: Guia passo a passo com exemplos

=HLOOKUP("Q1", A1:D3, 2, FALSE)

A função HLOOKUP acima procura por "Q1" na linha 1 e retorna o valor da linha 2 na mesma coluna. 

Vamos executar a função e ver os resultados.

Como você pode ver acima, a função deve retornar 1200. Como a função procura o valor encontrado na linha 2 com base no valor especificado na linha 1, 1200 é o resultado correto que estamos procurando.

As funções INDEX e MATCH

A seguir, vamos ver como as funções INDEX e MATCH do Excel se combinam para procurar valores. 

Sintaxe e exemplos de uso

=INDEX(array, row_num, [column_num])

A função INDEX retorna o valor em uma linha e coluna específicas em um determinado intervalo.

=MATCH(lookup_value, lookup_array, [match_type])

A função MATCH retorna a posição relativa de lookup_value em lookup_array.

Usando INDEX e MATCH juntos

Quando as funções INDEX e MATCH são usadas juntas, elas fornecem uma ferramenta poderosa para que você procure valores em uma tabela ou intervalo. Essa combinação permite que você especifique o local exato do valor que deseja retornar, em vez de ter que inserir manualmente os números de linha e coluna.

Vejamos um exemplo:

=INDEX(B2:B10, MATCH("John", A2:A10, 0))

Essa combinação encontra a linha em que "John" aparece em A2:A10 e, em seguida, retorna o valor correspondente de B2:B10.

Vamos ver o que a função produzirá.

Quando as duas funções são usadas juntas, elas obtêm o mesmo resultado que a função VLOOKUP. No entanto, o uso de INDEX e MATCH permite mais flexibilidade na escolha da coluna a ser retornada, além de permitir que você pesquise em várias colunas.

A função XLOOKUP (Excel 2019 e posteriores)

Por fim, vamos dar uma olhada em uma das mais novas funções de pesquisa do Excel, o XLOOKUP.

Sintaxe e parâmetros

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Exemplos práticos

Aplicaremos a função em um conjunto de dados simples e em um exemplo.

=XLOOKUP("John", A2:A10, B2:B10, "Not Found")

A função acima procura por "John" em A2:A10 e retorna o valor de B2:B10; se estiver faltando, retorna "Not Found".

Observando a saída da fórmula fornecida acima, podemos ver que a saída "Not Found" foi exibida. Isso significa que não existe uma entrada com o nome de funcionário "John".

Comparação: LOOKUP vs. VLOOKUP vs. XLOOKUP

Vamos juntar todas essas funções para ver como elas se combinam e analisar suas diferenças.

Conclusão

As funções de pesquisa são ferramentas essenciais no Excel para a recuperação eficiente de dados. Embora o LOOKUP, o VLOOKUP e o HLOOKUP tenham sua utilidade, o INDEX/MATCH e o XLOOKUP oferecem mais flexibilidade e poder. Entender quando e como usar cada um deles pode aumentar significativamente a eficiência de sua planilha e seus recursos analíticos.

Se você estiver procurando por mais recursos sobre o Excel, nosso Curso de preparação de dados no Excel seria um ótimo lugar para você começar. Para você dominar os fundamentos do Excel, nosso programa de habilidades Fundamentos do Excel é o melhor lugar para você começar. Você também pode conferir nossos tutoriais de Excel sobre Fórmulas básicas do Excel ou Vlookup de outras planilhas no Excel também.