Pesquisar Texto ao Digitar no Excel em Lista de Validação

Pesquisar Texto ao Digitar no Excel em Lista de Validação

Neste artigo você aprenderá pesquisar um texto ao digitar no Excel em uma lista de validação de dados.

O objetivo é ao digitar alguns caracteres a lista encontrar as células que contém a sequência de letras e na você poder selecionar na lista o item desejado.

Lista de validação Excel Pesquisa ao digitar

Como visto acima, você digita algumas letras e pressiona Enter ou Ctrl+Enter, para não sair da célula e são listados os dados que contém aquele texto.

Como Criar uma Validação de Dados Pesquisável no Excel

Há mais de uma forma de fazer esta validação de dados pesquisável.

A forma de pesquisa que utilizamos neste artigo é com o Office 365 e as funções de matrizes dinâmicas, principalmente a função FILTRO.

Para versões mais antigas veja como fazer isso com VBA: https://www.guiadoexcel.com.br/planilha-filtro-automatico-excel-ao-digitar/

Criando a base de dados

A base de dados que usaremos na nossa planilha com filtro automático de dados no Excel ao digitar será a de endereços abaixo.

A lista deve estar no formato tabular ou em formato de tabela, conforme ilustramos abaixo.

Validação de dados com lista pesquisável no Excel 1

O nosso objetivo é listar pelas letras digitadas os nomes da coluna Local em uma outra planilha:

Validação de dados com lista pesquisável no Excel 2

Na outra planilha acima, digite os dados conforme o mesmo cabeçalho da anterior, na linha abaixo dela iremos colocar a validação para digitar os dados.

Na planilha de base de dados na célula L9 digitamos =Tabela!B10 que é o endereço da planilha aonde serão listados os dados.

Na planilha com a base de dados inclua a seguinte fórmula:

=CLASSIFICAR(FILTRO(B10:B27;(ÉNÚM(LOCALIZAR(L9;B10:B27)));”NÃO ENCONTRADO”))

A fórmula funciona da seguinte forma:

A função Localizar identifica se o texto contido na célula L9, que está ligada com a célula aonde foi digitado o texto é encontrada em cada uma das palavras do intervalo em B10:B27.

Caso encontre a posição inicial do texto é retornada em formato de número.

Então usamos a função ÉNÚM para identificar se foi retornado um número retornando então uma lista de VERDADEIRO e FALSO.

A função FILTRO é então aplicada no intervalo de B10:B27 retornando os seus correspondentes apenas quando for VERDADEIRO no filtro aplicado.

E por último é realizada a classificação dos dados utilizando a função CLASSIFICAR e caso não encontrada informação é retornado NÃO ENCONTRADO.

Validação de dados com lista pesquisável no Excel 3

Assim é retornada uma lista com os dados desta planilha automaticamente logo abaixo da célula.

Retorne então para a outra planilha, aonde teremos a pesquisa dos dados.

Nela selecione a célula aonde irá adicionar a validação de dados e clique na guia Dados->Validação de dados e selecione a opção Permitir Lista.

Aponte para a célula L10 da outra planilha e digite # ao final no campo Fonte, conforme abaixo.

Validação de dados com lista pesquisável no Excel 4

O # faz com que os dados sejam entendidos como uma matriz de dados, listando assim todos os itens que estiverem no retorno desta fórmula.

Ainda na Validação de dados desmarque a opção Mostrar alerta de erro após a inserção de dados inválidos no Excel.

Esta opção desmarcada faz com que não sejam permitidos quaisquer dados, e como precisamos digitar textos parciais precisamos desabilitar esta opção.

Validação de dados com lista pesquisável no Excel 5

Então agora na célula digite uma parte do texto que deseja pesquisar para que ele complete a validação de dados e pressione CTRL+ENTER e em seguida CTRL+Seta para baixo, ou então ENTER e clique na lista do canto direito.

Lista de validação Excel Pesquisa ao digitar

Veja que os dados agora são listados conforme a informação fórmula da planilha da base dados.

Função Filtro como Procv no Excel

A função Filtro é muito útil realmente.

Conheça mais sobre esta função em: https://www.guiadoexcel.com.br/relatorio-excel-com-matrizes-dinamicas/

Na nossa planilha de validação de dados pesquisável no Excel, nós iremos usar a função filtro para retornar a linha com a empresa selecionada.

Para isso usamos a seguinte fórmula:

=FILTRO(Origem!C10:I27;(Origem!B10:B27=Tabela!B10);””)

Na função Filtro estamos retornando as informações de C10:I27 da nossa base de dados chamada Origem, quando os dados da coluna de nome da empresa B10:B27 for igual ao conteúdo digitado em B10 na nossa tabela.

Validação de dados com lista pesquisável no Excel 6

O resultado é o seguinte:

Validação de dados com lista pesquisável no Excel 7

Ao selecionar o local, automaticamente são retornadas todas as informações pertinentes aos endereços de uma forma automática para a nossa consulta.

Esta solução está limitada a uma linha, mas em breve posto uma adaptação para que possa ser aplicada em mais células.

Download

Realize o download do arquivo deste exemplo e da vídeo-aula acima neste botão abaixo. Basta se inscrever na nossa newsletter gratuita para o download automático.

Baixe a planilha

Curso Excel Completo – Do Básico ao VBA

Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo:


Marcos Rieper

Pai, marido, professor e consultor em Excel.

Obrigado por ler este artigo, este blog foi criado para difundir o conhecimento em Excel à todos.

Divulgamos novos artigos nas redes sociais, basta clicar nos ícones abaixo.

Excel não precisa ser complicado

Assine nossa newsletter e receba dicas práticas para dominar o excel