Como pesquisar vários valores no Excel: passo a passo

Ao trabalhar com grandes conjuntos de dados no Microsoft Excel, pode ser difícil encontrar vários valores que correspondam a várias condições. Algumas funções incorporadas, por exemplo VLOOKUP, foram originalmente projetadas para funcionar com valores únicos.

Várias funções do Excel podem ser combinadas para pesquisar vários valores. Eles incluem as funções VLOOKUP, INDEX, MATCH e IF. As versões atuais do Excel podem usar matrizes dinâmicas, enquanto as versões mais antigas usam fórmulas de matriz.

Este artigo mostrará exatamente como usar essas funções em fórmulas que encontram vários valores em seus dados.

Vamos!

Índice

• Como usar VLOOKUP com vários valores
  • O que são fórmulas de matriz?
  • Exemplo de VLOOKUP
  • Versões mais recentes do Excel
  • Versões mais antigas
  • Correspondência exata x correspondência aproximada
  • Mais sobre VLOOKUP
• Como usar INDEX e MATCH para pesquisar vários valores
  • Como usar ÍNDICE e CORRESP juntos no Excel 365
  • Trabalhando com versões mais antigas do Excel
• Como usar a função SE para encontrar vários valores
  • Versões Atuais do Excel
  • Versões mais antigas do Excel
• Como usar SUMPRODUCT para vários critérios
• 3 erros comuns com funções de pesquisa
  • 1. #N/A Erros
  • 2. #REF! Erros
  • 3. Erros circulares
• Como usar IFERROR com funções de pesquisa
• 7 técnicas avançadas de pesquisa
  • 1. Pesquisa de posição relativa
  • 2. Função PEQUENA
  • 3. Função LINHA
  • 4. Função FILTRO
  • 5. Colunas Auxiliares
  • 6. Matrizes Dinâmicas
  • 7. Consulta de Poder
• Pensamentos finais

Como usar VLOOKUP com vários valores

A função VLOOKUP é freqüentemente usada para encontrar valores únicos em um intervalo de dados. No entanto, você também pode pesquisar várias correspondências no Excel com esta fórmula de pesquisa.

Por padrão, ele retornará apenas o primeiro valor correspondente que encontrar. No entanto, você pode modificar a função para retornar vários valores usando uma fórmula de matriz.

O que são fórmulas de matriz?

Uma fórmula de matriz é uma fórmula que pode realizar cálculos em matrizes de dados. É chamada de fórmula de matriz porque pode retornar uma matriz de resultados, em vez de um único valor.

Existem várias etapas fáceis ao criar uma fórmula de matriz:

1. Selecione um intervalo de células para pesquisar.

2. Insira a fórmula na barra de fórmulas.

3. Pressione Ctrl + Shift + Enter para completá-lo.

A sintaxe de uma fórmula de matriz é semelhante à de uma fórmula regular, mas inclui chaves {} ao redor da fórmula. As chaves indicam que a fórmula é uma fórmula de matriz e que retornará uma matriz de valores.

Os exemplos neste artigo mostrarão como usar fórmulas de matriz corretamente.

Exemplo de VLOOKUP

Nosso exemplo possui cinco itens na coluna A de uma planilha:

• Maçã

• Banana

• Pão

• Cenoura

• Cereja

A tarefa é verificar se três frutas específicas estão nesta lista: maçã, banana e cereja.

Versões mais recentes do Excel

A sintaxe VLOOKUP depende de qual versão do Excel você está usando.

As versões mais recentes ( Excel 365 ou Excel 2021 ) suportam arrays dinâmicos. Esse recurso permite que as fórmulas retornem vários resultados que “espalham” em células adjacentes.

Esta é a sintaxe (o sinal de igual inicia a fórmula):

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

• lookup_value: O valor que você deseja pesquisar.

• table_array: Toda a tabela de dados que você deseja pesquisar.

• col_index_num: O número da coluna da tabela no table_array que contém os dados que você deseja retornar.

• intervalo_lookup: Opcional. Especifica se você deseja uma correspondência exata ou aproximada.

Nosso exemplo específico usa esta fórmula:

=PROCV({“maçã”,”banana”,”cereja”},A1:A5,1,FALSO)

Quando a fórmula é inserida na célula B1, os resultados se espalham nas células C1 e D1. Esta imagem mostra o exemplo em ação:

Versões mais antigas

Se você estiver usando uma versão mais antiga do Excel que não oferece suporte a matrizes dinâmicas (por exemplo, Excel 2019 ou anterior), precisará usar uma abordagem ligeiramente diferente com uma fórmula de matriz.

Siga esses passos:

1. Clique na célula onde deseja exibir os resultados do primeiro item (por exemplo, coluna B).

2. Digite a seguinte fórmula sem pressionar Enter ainda: =VLOOKUP({“apple”,”banana”,”cherry”}, A1:A5, 1, FALSE)

3. Pressione Ctrl + Shift + Enter para transformar isso em uma fórmula de matriz.

4. Copie a célula B1 e cole-a na célula abaixo (ou use as alças de preenchimento).

Quando você usa Ctrl + Shift + Enter, o Excel adiciona chaves ao redor da fórmula. Isso indica que é uma fórmula de matriz.

Correspondência exata x correspondência aproximada

Por padrão, a função VLOOKUP usa uma correspondência aproximada. Isso significa que ele retornará a correspondência mais próxima possível, mesmo que os valores da célula não sejam uma correspondência exata.

Se você deseja realizar uma correspondência exata, pode definir o argumento range_lookup como FALSE.

Lembre-se de que correspondências aproximadas funcionam melhor com valores numéricos ordenados. Geralmente não é apropriado quando o valor da célula é texto.

Mais sobre VLOOKUP

Se você quiser saber mais sobre essa função versátil, confira estes artigos:

• como comparar duas colunas no Excel com VLOOKUP

• como encontrar discrepâncias com VLOOKUP

Agora que você está configurado com a função VLOOKUP, vamos dar uma olhada em duas outras funções que podem fazer o que ela faz de uma maneira diferente: INDEX e MATCH.

Como usar INDEX e MATCH para pesquisar vários valores

Você pode combinar as funções INDEX e MATCH para encontrar vários valores em várias linhas.

A função ÍNDICE no Excel retorna um valor ou referência a uma célula dentro de um intervalo especificado.

=ÍNDICE(array, núm_linha, [núm_coluna])

• array: O intervalo de células a serem pesquisadas para o valor.

• núm_linha: O número da linha dentro da matriz da qual retornar um valor.

• column_num: (Opcional) O número da coluna dentro da matriz da qual retornar um valor. Se omitido, a função retorna a linha inteira.

A função CORRESP no Excel retorna a posição de um valor dentro de um intervalo especificado.

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value: O valor a ser pesquisado dentro do lookup_array.

• lookup_array: O intervalo de células a serem pesquisadas para o lookup_value.

• match_type: (Opcional) O tipo de correspondência a ser realizada. Se omitido, a função executa uma correspondência exata.

Como usar ÍNDICE e CORRESP juntos no Excel 365

Para usar INDEX e MATCH juntos para pesquisar vários valores no Excel, você precisa usar uma fórmula de matriz.

Trabalhando com os dados de amostra anteriores, esta é a fórmula no Excel 365:

=ÍNDICE(A1:A5, CORRESP({“maçã”,”banana”,”cereja”}, A1:A5, 0))

O exemplo acima se divide assim:

• ÍNDICE: retorna o valor de uma célula em um intervalo especificado com base em uma determinada linha e número de coluna. Neste caso, retornará o valor do intervalo A1:A5.

• A1:A5: Este é o intervalo de tabela definido onde você está procurando o valor e do qual o resultado será retornado.

• CORRESP: pesquisa um item especificado em um intervalo de células e retorna a posição relativa desse item no intervalo.

• {“apple”,”banana”,”cherry”}: esta é a constante de array contendo os valores que você deseja procurar.

• A1:A5: este é o intervalo onde MATCH irá procurar os valores da constante do array.

• 0: este é o tipo de correspondência para a função MATCH. Nesse caso, é 0, o que significa que você está procurando uma correspondência exata em vez de uma correspondência aproximada.

 Esta imagem mostra a fórmula em ação:

Trabalhando com versões mais antigas do Excel

Se você estiver usando um arquivo Excel mais antigo que não oferece suporte a matrizes dinâmicas (por exemplo, Excel 2019 ou anterior), precisará usar uma abordagem diferente.

Como as versões mais antigas não oferecem suporte a fórmulas "derramando" em células adjacentes, você precisará dividir o uso em três fórmulas separadas.

Siga esses passos:

1. Clique na célula onde deseja o resultado para o primeiro item (por exemplo, célula B1)

2. Digite a fórmula abaixo:

3. =ÍNDICE(A1:A5, CORRESPONDÊNCIA(“maçã”, A1:A5, 0))

4. Pressione Enter para executar a fórmula.

5. Digite esta fórmula na célula B2: =ÍNDICE(A1:A5, CORRESP(“banana”, A1:A5, 0))

6. Digite esta fórmula na célula B3: =ÍNDICE(A1:A5, CORRESP(“cereja”, A1:A5, 0))

Esta imagem mostra a referência de célula B3:

As funções INDEX e MATCH não são as únicas que podem permitir que você encontre vários valores. Na próxima seção, veremos como você pode usar a função SE como alternativa.

Como usar a função SE para encontrar vários valores

Outra maneira de pesquisar vários valores de células com base em determinados critérios é usar a função SE com outras funções.

A função SE permite testar várias condições e retornar resultados diferentes, dependendo do resultado desses testes.

Por exemplo, digamos que você tenha uma tabela de dados de vendas com colunas para Produto e Vendas. Você deseja pesquisar e totalizar o valor das vendas de dois dos três produtos.

Versões Atuais do Excel

Para encontrar a soma da coluna Vendas onde o produto é “Apple” ou “Banana” usando a função SE, você pode usar uma fórmula de matriz com funções SE, SOMA e OU.

Supondo que seus dados comecem na célula A1, use a seguinte fórmula:

=SOMA(SE((A2:A4=”Maçã”)+(A2:A4=”Banana”), B2:B4, 0))

A seção (A2:A4=”Apple”)+(A2:A4=”Banana”) cria um array que tem valor 1 se a célula no intervalo A2:A4 contiver “Apple” ou “Banana”, e 0 caso contrário .

A instrução IF verifica cada elemento do argumento da matriz. Se o valor for 1 (ou seja, o produto é “Maçã” ou “Banana”), assume o valor correspondente na coluna Vendas (intervalo B2:B4); caso contrário, leva 0.

A função SUM soma os valores da função SE, somando efetivamente os valores de vendas para “Apple” e “Banana”.

Esta imagem mostra a fórmula em ação no intervalo de pesquisa:

Versões mais antigas do Excel

No Excel 2019 ou anterior, você precisa usar uma fórmula de matriz. Siga esses passos:

1. Digite a fórmula, mas não pressione Enter.

2. Pressione Ctrl + Shift + Enter para torná-lo uma fórmula de matriz.

O Excel adicionará colchetes {} ao redor da fórmula, indicando que é uma fórmula de matriz.

A seguir, veremos como você pode usar SUMPRODUCT para pesquisar vários valores com base em seus critérios. Vamos!

Como usar SUMPRODUCT para vários critérios

A função SUMPRODUCT também permite pesquisar vários valores com base em vários critérios.

Por não exigir o uso de uma fórmula de matriz, a sintaxe é a mesma, independentemente da versão do Excel.

Usando os mesmos dados do exemplo anterior, a fórmula fica assim:

=SOMAPRODUTO((A2:A4=”Maçã”)+(A2:A4=”Banana”), B2:B4)

A seção (A2:A4=”Apple”)+(A2:A4=”Banana”) cria um array que tem valor 1 se a célula no intervalo A2:A4 contiver “Apple” ou “Banana”, e 0 caso contrário .

A função SUMPRODUCT multiplica os elementos do array com os elementos correspondentes na coluna Sales (intervalo B2:B4). Em seguida, soma os valores resultantes, somando efetivamente os valores de vendas para “Apple” e “Banana”.

A fórmula abaixo mostra isso em ação:

As funções do Excel são incríveis quando funcionam conforme o esperado, mas às vezes você pode encontrar erros. Na próxima seção, cobrimos alguns dos erros comuns e como você pode lidar com eles.

3 erros comuns com funções de pesquisa

Às vezes, as funções de pesquisa podem retornar erros que podem ser frustrantes e demorados para solucionar problemas. Os três erros mais comuns que você encontrará são:

1. #N/D Erros

2. #REF! Erros

3. Erros circulares

1. #N/A Erros

O erro #N/A ocorre quando o valor de pesquisa não pode ser encontrado na matriz de pesquisa.

Existem vários motivos pelos quais esse erro pode ocorrer, incluindo:

• o valor de pesquisa está incorreto ou incorreto.

• a matriz de pesquisa não é classificada em ordem crescente.

• o valor de pesquisa não está no conjunto de dados.

Quando o valor de pesquisa não está no conjunto de dados, esta é uma informação útil. No entanto, usuários inexperientes do Excel podem pensar que #N/A significa que algo deu errado com a fórmula. A próxima seção mostra como tornar isso mais fácil de usar.

2. #REF! Erros

A #REF! ocorre quando a matriz de pesquisa ou matriz de retorno é excluída ou movida.

Esse erro pode ser corrigido atualizando as referências de célula na função de pesquisa.

3. Erros circulares

Conforme você combina funções em fórmulas complexas, o Excel pode informar que você tem uma referência circular.

Você achará isso mais fácil de investigar usando nosso guia para localizar referências circulares no Excel .

Como usar IFERROR com funções de pesquisa

A função SEERRO é uma ferramenta útil para lidar com erros em funções de pesquisa. Ele permite que você especifique um valor ou fórmula para retornar se a função de pesquisa retornar um erro.

A sintaxe da função SEERRO é a seguinte:

=SEERRO(valor, valor_se_erro)

· valor: valor ou fórmula que se deseja avaliar.

· Value_if_error: o valor ou fórmula a retornar se o primeiro argumento retornar um erro.

Por exemplo, digamos que você tenha uma função VLOOKUP que está procurando vários valores em uma tabela. Na figura abaixo, um dos valores não existe no intervalo de dados pesquisado.

Como você pode ver, o erro #N/A é exibido, o que pode confundir usuários inexperientes do Excel.

Em vez disso, você pode usar SEERRO para exibir uma célula em branco ou uma mensagem que diz “Não encontrado” com esta sintaxe:

=SEERRO(VLOOKUP(lookup_value, table_array, column_index, FALSE), “Não encontrado”)

Neste exemplo, se a função VLOOKUP retornar um erro, a função SEERRO retornará a mensagem “Não encontrado”.

Esta imagem mostra a fórmula em ação. A coluna B tem o valor ausente, enquanto a coluna C e a coluna D encontraram correspondências.

Cobrimos muito terreno até agora e você finalmente está pronto para aprender técnicas mais avançadas para pesquisas, que é o tópico da próxima seção.

7 técnicas avançadas de pesquisa

Procurar vários valores no Excel pode ser uma tarefa desafiadora, especialmente ao lidar com grandes conjuntos de dados. Você pode encontrar problemas de desempenho com processamento lento.

Existem sete técnicas avançadas de pesquisa que você pode usar para tornar o processo mais fácil e eficiente.

• Pesquisa de posição relativa

• função PEQUENA

• função LINHA

• Função FILTRO

• Colunas auxiliares

• Matrizes Dinâmicas

• Consulta avançada

1. Pesquisa de posição relativa

Uma das maneiras mais simples de pesquisar vários valores no Excel é usando a pesquisa de posição relativa. Isso envolve especificar os deslocamentos de linha e coluna de uma determinada célula para localizar o valor desejado.

Por exemplo, se você tiver uma tabela de dados e quiser pesquisar um valor que esteja duas linhas abaixo e três colunas à direita de uma determinada célula, poderá usar a seguinte fórmula:

=DESLOC(célula, 2, 3)

2. Função PEQUENA

Outra técnica útil para pesquisa de vários valores é usar a função SMALL. Esta função retorna o enésimo menor valor em um intervalo de células.

Ao combinar esta função com outras funções de pesquisa, como INDEX e MATCH, você pode pesquisar vários valores com base em critérios específicos.

Por exemplo, a fórmula a seguir procura o segundo menor valor em um intervalo de células que atendem a uma determinada condição:

=ÍNDICE(dados, CORRESPONDÊNCIA(PEQUENO(SE(critérios, intervalo), 2), intervalo, 0))

3. Função LINHA

A função LINHA também pode ser usada para pesquisas de vários valores no Excel. Esta função retorna o número da linha de uma determinada célula, que pode ser usada para referenciar células em uma tabela de dados.

Por exemplo, a fórmula a seguir procura um valor em uma tabela com base em um identificador exclusivo:

=ÍNDICE(dados, CORRESP(identificador_exclusivo, dados[identificador_exclusivo], 0), número_índice_da_coluna)

4. Função FILTRO

A função FILTER não está disponível em versões mais antigas do Excel.

No Excel 365, você pode usá-lo para filtrar um intervalo de células com base em determinados critérios e retornar apenas os valores que atendem a esses critérios. Esta é a sintaxe e três argumentos:

=FILTRO(array, include, [if_vazio])

• array: Os dados específicos que você deseja filtrar.

• include: O argumento include são os critérios ou condições que você deseja aplicar ao array.

• [if_empty] (opcional): o valor a ser retornado se nenhuma linha ou coluna atender aos critérios especificados no argumento de inclusão.

Por exemplo, a fórmula a seguir funciona nos dados de exemplo para encontrar correspondências para dois dos três itens na primeira coluna e somar seus valores correspondentes na segunda coluna.

=SOMA(FILTRO(B2:B4, (A2:A4=”Maçã”)+(A2:A4=”Banana”)))

Esta imagem mostra quantas linhas são correspondidas e a soma dos diferentes valores:

5. Colunas Auxiliares

Você pode usar uma coluna auxiliar para unir vários campos em uma função como VLOOKUP.

Suponha que você esteja trabalhando com nomes e sobrenomes em uma folha separada. Você pode concatená-los em uma coluna auxiliar referenciada na fórmula final.

6. Matrizes Dinâmicas

Como você aprendeu nos exemplos anteriores, os usuários do Microsoft 365 podem aproveitar as matrizes dinâmicas para pesquisa de vários valores no Excel.

As matrizes dinâmicas permitem que você retorne vários valores de uma única fórmula, facilitando a pesquisa de grandes quantidades de dados.

7. Consulta de Poder

O Power Query é uma ferramenta poderosa no Excel que pode ser usada para retornar valores com base em vários critérios.

Por exemplo, este vídeo encontra dados confusos em uma planilha e os limpa.

Pensamentos finais

Isso encerra nosso mergulho profundo na arte de procurar vários valores no Excel! Depois de dominar as fórmulas VLOOKUP, INDEX, MATCH e array, você se sentirá navegando por conjuntos de dados complexos como uma faca quente na manteiga.

A chave é entender a sintaxe e a lógica por trás de cada fórmula. Lembre-se de que essas fórmulas podem ser complexas, por isso é importante levar o seu tempo e testar suas fórmulas completamente antes de confiar nelas para análise de dados importantes.

O Excel é uma ferramenta poderosa, mas é tão boa quanto o usuário. Portanto, continue aprimorando essas habilidades, continue experimentando e logo você será o mestre das pesquisas de vários valores. Até a próxima vez, continue processando esses números e fazendo o Excel fazer o trabalho duro para você!