6 fórmulas para pesquisar no Excel

Anonim

Todos nós conhecemos a famosa estrela das funções Excel VLOOKUP. É comumente usado para procurar valores com um ID exclusivo. Mas essa não é a única função que pode ser usada para pesquisar valores no Excel. Existem muitas outras funções e fórmulas que podem ser usadas para pesquisar o valor. Neste artigo, apresentarei todas essas funções e fórmulas de pesquisa do Excel. Alguns são ainda melhores do que a função VLOOKUP do Excel. Então, leia até o fim.

1. A função VLOOKUP do Excel

A primeira função de pesquisa do Excel é, obviamente, a função VLOOKUP. Essa função é famosa por um motivo. Podemos usar essa função para fazer mais do que apenas uma pesquisa. Mas a tarefa básica dessa função é pesquisar valores na tabela, da esquerda para a direita.

Sintaxe da função PROCV:

= PROCV (lookup_value, table_array, col_index_number, [Pesquisa de alcance])

Valor_procurado: O valor pelo qual você deseja pesquisar na primeira coluna da Matriz da tabela.

Table_array: A tabela na qual você deseja procurar / pesquisar

col_index_number: O número da coluna em Table Array da qual você deseja buscar resultados.

[Pesquisa de alcance]: FALSE se quiser pesquisar o valor exato, TRUE se quiser uma correspondência aproximada.

Vantagens de VLOOKUP:

  • Fácil de usar.
  • Rápido
  • Uso múltiplo
  • Melhor para procurar valores em ordem vertical.

Desvantagens:

  • É usado apenas para pesquisa vertical
  • Ele retorna apenas o primeiro valor correspondido.
  • Estático até ser usado com a função MATCH.
  • Não é possível pesquisar valores à esquerda do valor de pesquisa.

Você pode ler mais sobre esta Fórmula de Pesquisa do Excel em detalhes aqui.

2. A função HLOOKUP do Excel

A função HLOOKUP é a parte ausente da função VLOOKUP. A função HLOOKUP é usada para pesquisar valores horizontalmente. Em outras palavras, quando você deseja pesquisar um valor no Excel combinando valores em colunas e obter valores de linhas, usamos a função HLOOKUP. Este é exatamente o propósito da função VLOOKUP.

Sintaxe de HLOOKUP

=HLOOKUP(valor de pesquisa, matriz de tabela, número de índice de linha, [range_lookup])
  • valor de pesquisa: O valor que você procura.
  • Matriz de tabela: A tabela na qual você está procurando o valor.
  • Número do índice da linha: O número da linha na tabela da qual você deseja recuperar os dados.
  • [Pesquisa de alcance] : é o tipo de correspondência. 0 para a correspondência exata e 1 para correspondência aproximada.

Vantagens da função HLOOKUP:

  • Ele pode pesquisar valores horizontalmente.
  • Fácil de usar.
  • Uso múltiplo
  • Rápido

Desvantagens:

  • É usado apenas para pesquisa horizontal
  • Ele retorna apenas o primeiro valor correspondido.
  • Estático até ser usado com a função MATCH.
  • Não é possível pesquisar valores acima dos valores de pesquisa na tabela.

Você pode aprender sobre esta função de pesquisa do Excel aqui.

3. A fórmula de pesquisa INDEX-MATCH

Onde VLOOKUP e HLOOKUP não podem alcançar, esta fórmula pode alcançar. Esta é a melhor fórmula de pesquisa no Excel até o Excel 2016 (XLOOKUP está a caminho).

A Fórmula Genérica de INDEX MATCH

= INDEX (Result_Range, MATCH (lookup_value, lookup range, 0))

Result_Range: É o intervalo de onde você deseja recuperar o valor.

Valor_procurado: É o valor que você deseja corresponder.

Lookup_Range:É o intervalo no qual você deseja corresponder ao valor de pesquisa.

Vantagens da fórmula de pesquisa INDEX-MATCH:

  • Pode pesquisar em quatro direções. Ele pode pesquisar valores à esquerda e acima do valor de pesquisa.
  • Dinâmico.
  • Não há necessidade de definir o índice de linha ou coluna.

Desvantagens:

  • Pode ser difícil para novos usuários.
  • Usa duas funções do Excel em combinação. Os usuários precisam entender o funcionamento das funções INDEX e MATCH.

Você pode aprender mais sobre essa fórmula aqui.

4: Fórmula de pesquisa Excel OFFSET-MATCH

Esta é outra fórmula que pode ser usada para pesquisar valores dinamicamente. Esta fórmula de pesquisa do Excel usa a função OFFSET como função âncora e MATCH como função alimentadora. Usando essa fórmula, podemos recuperar valores de uma tabela dinamicamente, procurando em linhas e colunas.

Fórmula Genérica,

= OFFSET (StartCell, MATCH (RowLookupValue, RowLookupRange, 0), MATCH (ColLookupValue, ColLookupRange, 0))

StartCell:Esta é a célula inicial da Tabela de pesquisa. Digamos que se você deseja pesquisar no intervalo A2: A10, então o StartCell será A1.

RowLookupValue: Este é o valor de pesquisa que você deseja encontrar nas linhas abaixo doStartCell.

RowLookupRange:Este é o intervalo no qual você deseja pesquisar RowLookupValue. É a faixa abaixo StartCell (A2: A10).

ColLookupValue: Este é o valor de pesquisa que você deseja encontrar nas colunas (cabeçalhos).

ColLookupRange:Este é o intervalo no qual você deseja pesquisar o ColLookupValue. É o intervalo do lado direito de StartCell (como B1: D1).

Vantagens desta técnica de pesquisa do Excel:

  • Rápido
  • Pode pesquisar horizontalmente e verticalmente.
  • Dinâmico

Desvantagens:

  • Complexo para algumas pessoas.
  • Precisa entender o funcionamento da função OFFSET e função MATCH.

Você pode aprender mais sobre essa fórmula de pesquisa aqui em detalhes.

5: Valores múltiplos da fórmula LOOKUP do Excel

Todas as fórmulas de pesquisa acima retornam o primeiro valor encontrado da matriz. Se houver mais de uma correspondência, eles não retornarão outras correspondências. Nesse caso, essa fórmula entra em ação para salvar o dia. Esta fórmula retorna todos os valores correspondentes da lista, em vez da primeira correspondência apenas.

Esta fórmula usa as funções INDEX, ROW e IF como funções principais. A função IFERROR pode ser usada opcionalmente para tratar erros.

Fórmula Genérica

{= INDEX (array, SMALL (IF (lookup_value = lookup_value_range, ROW (lookup_value_range) -ROW (primeira célula de lookup_value_range) +1), ROW (1: 1)))}

Variedade: O intervalo de onde você deseja buscar dados.
lookup_value: Seu lookup_value que você deseja filtrar.
lookup_value_range: O intervalo no qual você deseja filtrar lookup_value.
A primeira célula no intervalo lookup_value: se seu intervalo de lookup_value é $ A $ 5: $ A $ 100 então é $ A $ 5.
Importante: Tudo deveria ser referenciado absoluto. lookup_value pode ser relativo de acordo com a necessidade.
Insira-o como uma fórmula de matriz. Depois de escrever a fórmula, pressione CTRL + SHIFT + ENTER para torná-la uma fórmula de matriz.

Como você pode ver no gif, ele retorna todas as correspondências da tabela do Excel.

Vantagens:

  • Retorna com vários valores correspondentes da tabela do Excel.
  • Dinâmico

Desvantagens:

  • É muito complexo para um novo usuário entender.
  • Usa fórmula de matriz
  • É necessário definir o número possível de resultados e aplicar esta fórmula como uma fórmula de matriz de várias células (não no Excel 2019 e 365).
  • Devagar.

Você pode aprender mais sobre essa fórmula aqui em detalhes.

6: VLOOKUP-CHOOSE Lookup Excel Formula

Portanto, a maioria das pessoas diz que não é possível pesquisar valores à esquerda do valor de pesquisa no Excel usando a função VLOOKUP. Bem, lamento dizer, mas eles estão errados. Podemos pesquisar à esquerda do valor de pesquisa no Excel usando a função VLOOKUP com a ajuda da função CHOOSE.

Fórmula Genérica:

= VLOOKUP (lookup_value, CHOOSE ({1, 2}, lookup_range, req_range), 2, 0)

lookup_value : valor a procurar

intervalo_de_procura : intervalo, onde procurar valor_procurado

req_range : intervalo, onde o valor correspondente é necessário

2 : segunda coluna, num representando o req_range

0 : procure a correspondência exata

Nesta fórmula, basicamente criamos uma tabela virtual dentro da fórmula usando a função CHOOSE. A função CHOOSE cria uma tabela de duas colunas. A primeira coluna contém o intervalo de pesquisa e a segunda coluna contém o intervalo de resultados.

Vantagens da fórmula de pesquisa VLOOKUP-CHOOSE:

  • Você pode pesquisar à esquerda do valor de pesquisa
  • Rápido
  • Fácil

Desvantagens:

  • A função ESCOLHER raramente é usada. Os usuários precisam entender seu funcionamento.

Você pode aprender mais sobre essa fórmula de pesquisa aqui.

Então, rapazes, essas são as diferentes funções e fórmulas de pesquisa. Isso não é tudo. Pode haver muito mais fórmulas de pesquisa no Excel que podem ser criadas usando diferentes combinações de fórmulas do Excel. Se você tiver alguma técnica de pesquisa especial, compartilhe na seção de comentários abaixo. Iremos incluí-lo em nosso artigo com o seu nome.

Espero que tenha sido útil e informativo. Se você tiver alguma dúvida em relação a este artigo, pergunte-me na seção de comentários abaixo.

Mais de 10 novas funções no Excel 2019 e 365: Embora as funções disponíveis no Excel 2016 e anteriores sejam suficientes para realizar qualquer tipo de cálculo e automação, às vezes as fórmulas ficam complicadas. Esses tipos de coisas pequenas, mas importantes, são resolvidos no Excel 2019 e 365.

17 coisas sobre VLOOKUP do Excel: A VLOOKUP não é apenas uma fórmula de pesquisa, é mais do que isso. A VLOOKUP tem muitas outras habilidades e pode ser usada para vários fins. Neste artigo, exploramos todos os usos possíveis da função VLOOKUP.

Mais de 10 gráficos de Excel avançados criativos para balançar seu painel: Excel é uma ferramenta de visualização de dados poderosa que pode ser usada para criar gráficos impressionantes no Excel. Esses 15 gráficos avançados do Excel podem ser usados ​​para hipnotizar seus colegas e chefes.

4 Gráficos de desempenho do criativo x objetivo no Excel: Os gráficos de objetivo x realização são os gráficos mais básicos e importantes em qualquer negócio. Portanto, é melhor colocá-los da maneira mais criativa possível. Esses 4 gráficos criativos podem fazer seus painéis agitarem a apresentação.

Artigos populares:

50 atalhos do Excel para aumentar sua produtividade | Torne sua tarefa mais rápida. Esses 50 atalhos farão você trabalhar ainda mais rápido no Excel.

Como usar a função VLOOKUP do Excel| Esta é uma das funções mais usadas e populares do Excel, que é usada para pesquisar valores em diferentes intervalos e planilhas.

Como usar o Excel Função CONT.SE| Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar valores específicos. A função Countif é essencial para preparar seu painel.

Como usar a função SUMIF no Excel | Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.