Tutorial do XMATCH no Excel: Visão geral, sintaxe e aplicativos práticos

Durante a análise de dados, há muitas situações em que você precisará encontrar a posição relativa de um item em uma matriz ou intervalo de células, considerando um determinado valor ou padrão. 

A maneira mais eficaz de realizar operações de correspondência de padrões no Excel é a função XMATCH. Uma função simples, porém poderosa, o XMATCH tem vários recursos que permitem uma correspondência precisa e flexível. 

Neste tutorial, abordaremos os conceitos básicos da função XMATCH do Excel, analisaremos sua sintaxe e diferentes parâmetros e forneceremos exemplos práticos para ilustrar seus recursos. Também compararemos o XMATCH com o MATCH, outra função popular usada para tarefas semelhantes.  Por fim, analisaremos as armadilhas comuns ao usar a função XMATCH e estabeleceremos as práticas recomendadas para garantir um uso sólido e eficiente.

Se você estiver em sua jornada de aprendizado do Excel e quiser saber mais, confira nosso programa de habilidades Fundamentos do Excel para que você aprenda todos os fundamentos.

O que é o XMATCH?

O XMATCH é uma função flexível e robusta para operações de correspondência. O site XMATCH() permite que você execute todos os tipos de operações de correspondência, desde pesquisas verticais e horizontais até correspondências exatas, aproximadas e parciais. 

Para aqueles que usam o Excel há muito tempo, você provavelmente conhece a função MATCH. Antes da atualização do Excel que introduziu o XMATCH, a função MATCH era a mais indicada para tarefas de correspondência. Você pode aprender sobre elas em nosso Tutorial de funções de pesquisa do Excel.

No entanto, como veremos mais adiante, o MATCH pode ser insuficiente em determinados cenários. Nesse sentido, o XMATCH pode ser visto como uma atualização aprimorada e há muito esperada da função MATCH tradicional, fornecendo pesquisa em qualquer direção e vários tipos de correspondência, tornando-a mais fácil e adequada para uma variedade maior de casos de uso.

Observe que a função está disponível apenas no Excel para Microsoft 365 e no Excel 2021. Para usuários com versões anteriores do Excel, a função MATCH ainda é a única disponível.

Vamos agora dar uma olhada na sintaxe e nos parâmetros do XMATCH. 

Sintaxe e parâmetros do XMATCH

Aqui está a sintaxe básica da função XMATCH:

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

A função é fornecida com dois argumentos obrigatórios (ou seja, lookup_value e lookup_array) e dois opcionais (match_mode e search_mode) que fornecem recursos para operações mais precisas e flexíveis.

Abaixo, você encontra uma descrição dos quatro parâmetros:

• Lookup_value: Esse é o valor que você deve procurar.
• Lookup_array: É a matriz ou lista onde você deve pesquisar. Pode ser uma matriz de pesquisa vertical ou horizontal.
• Match_mode: permite que você escolha o tipo de correspondência. Há quatro modos, incluindo
• 0: Correspondência exata (o modo padrão)
• -1: Correspondência exata ou o próximo menor item
• 1: Correspondência exata ou o próximo maior item
• 2: Uma correspondência curinga, como * e ?
• Search_modepermite que você especifique o modo de pesquisa, incluindo a direção e o algoritmo da pesquisa. Há quatro valores possíveis:
• 1: Pesquisar do primeiro ao último (o valor padrão)
• -1: Pesquisar do último para o primeiro (pesquisa reversa).
• 2:  Pesquisa binária ascendente. A pesquisa é realizada supondo que a matriz de pesquisa esteja classificada em ordem crescente; caso contrário, ela retorna resultados inválidos.
• -2:  Pesquisa binária descendente. A pesquisa é realizada supondo que a matriz de pesquisa esteja classificada em ordem decrescente; caso contrário, ela retorna resultados inválidos.

Modos de partida: Precisão e flexibilidade

O XMATCH vem com um parâmetro match_mode opcional que permite que você escolha o tipo de correspondência. Essas opções são muito úteis, pois oferecem maneiras de conduzir tanto a correspondência precisa quanto a correspondência aproximada. Vamos analisar em detalhes os modos de jogo disponíveis:

Correspondência exata

Correspondência exata (match_mode = 0 )é o modo de correspondência padrão do XMATCH. Isso significa que o Excel encontrará uma correspondência exata do valor de pesquisa na matriz de pesquisa; caso contrário, ele retornará um erro NA. A correspondência exata é essencial quando a exatidão e a precisão são fundamentais, como encontrar entradas específicas em um banco de dados.

Aqui está um exemplo básico de como você pode usar a função XMATCH para encontrar a posição exata de "Excel" em uma lista de tecnologias disponíveis no catálogo de cursos da DataCamp. Fornecemos o valor de pesquisa em D3 e a matriz de pesquisa (A3:A14), e o Excel fornece corretamente a posição 6. 

Correspondência aproximada

Pode haver situações em que você precise fazer uma correspondência flexível, seja porque não sabe o valor exato da pesquisa ou porque está procurando resultados aproximados. 

Felizmente, o XMATCH vem com dois modos de correspondência que permitem que você execute valores exatos ou o menor valor seguinte e valores exatos ou o maior valor seguinte. 

No exemplo a seguir, usamos a correspondência aproximada para encontrar as contagens de cursos mais próximas do valor de pesquisa 15. 

Ao usar o próximo menor (match_mode = -1), o Excel retorna a posição 6, pois 12 cursos é o menor número mais próximo na lista. No caso do próximo maior, o Excel retorna 5, pois 16 cursos é o maior número mais próximo.

Também usamos a correspondência exata para ilustrar o que acontece quando o Excel não consegue encontrar uma correspondência exata.

Correspondências curinga e regex

Um recurso inovador do XMATCH é a possibilidade de usar curingas básicos quando você define match_mode como 2, bem como expressões regulares mais avançadas se definido como 3. Esses modos de correspondência só funcionam com dados de texto.

O XMATCH permite que você use dois curingas populares:

• ?: para corresponder a qualquer caractere único.
• *: para corresponder a qualquer sequência de caracteres.

Abaixo, você encontra vários exemplos de como usar correspondências regex. No primeiro exemplo, procuramos tecnologias que começam com E. No segundo, procuramos tecnologias com A no nome. Por fim, procuramos tecnologias com três letras.

Observe que o XMATCH sempre retornará a primeira ocorrência na matriz de pesquisa, a menos que especifiquemos de forma diferente (consulte a próxima seção).

Se você quiser usar expressões regulares mais complexas, altere match_mode para 3. As expressões regulares, geralmente abreviadas como "regex" ou "regexp", são uma forma de definir um padrão de pesquisa, que pode ser usado para várias tarefas de manipulação de texto, como pesquisa, análise e/ou substituição de texto. 

Para obter mais detalhes sobre a correspondência de padrões, consulte nosso Excel Regex Tutorial: Mastering Pattern Matching with Regular Expressions é um ótimo lugar para você começar.

Aqui está um exemplo bastante simples: Queremos encontrar a posição de um curso cujo código inclui três letras no início, três no final e dois números no meio (31).

Modos de pesquisa do XMATCH: Direção e eficiência

O XMATCH apresenta um novo parâmetro que permite que você controle a direção da pesquisa. Esse recurso é excelente quando você está mais interessado em encontrar a última ocorrência do que a primeira, por exemplo, para determinadas tarefas na análise de séries temporais. 

Vamos explorar em detalhes os diferentes modos de pesquisa.

Pesquisa reversa

Para executar uma pesquisa reversa, você pode definir o parâmetro search_mode como -1. Por exemplo, abaixo você pode ver como encontrar o primeiro e o último curso concluído pelo usuário Peter.

Pesquisa binária

Por fim, o parâmetro search_mode também oferece a possibilidade de usar um algoritmo de pesquisa binária para realizar a operação de correspondência. Pesquisa binária é um algoritmo de classificação que funciona dividindo repetidamente o intervalo de pesquisa pela metade. É um dos algoritmos de classificação mais eficazes, mas requer que os dados de entrada sejam classificados anteriormente. 

O XMATCH permite que você aproveite a pesquisa binária com uma matriz classificada em ordem crescente (search_mode = 2) e decrescente (search_mode = -2). Caso a matriz de pesquisa não esteja classificada, a função lançará um erro.

Aplicativos práticos e exemplos do XMATCH

O XMATCH é adequado para uma ampla gama de tarefas do Excel, desde as básicas até as avançadas. Nesta seção, abordamos algumas das aplicações práticas mais interessantes.

Pesquisas bidimensionais com INDEX/XMATCH

As funções INDEX e XMATCH podem trabalhar em conjunto para realizar pesquisas bidimensionais, ou seja, pesquisas em linhas e colunas simultaneamente. Ao combinar essas duas funções, podemos criar uma pesquisa dinâmica que funciona muito bem em domínios, como uma análise financeira, em que é muito comum comparar variáveis entre categorias. 

Por exemplo, na imagem abaixo, combinamos INDEX e MATCH para extrair o número de usuários inscritos nos cursos do Excel durante o segundo trimestre. INDEX recebe três argumentos: a matriz onde você deseja encontrar o valor, o número da linha e o número da coluna. Os valores dos dois últimos argumentos são fornecidos por várias funções XMATCH.

 Se quisermos alterar a tecnologia em questão ou o trimestre, você só precisa alterar os valores de pesquisa nas funções XMATCH, e o resultado do INDEX será alterado ao longo do caminho.

O INDEX/XMATCH funciona de maneira muito semelhante ao INDEX/MATCH. Se você quiser saber mais sobre esse poderoso conjunto com exemplos práticos, confira nosso artigo como fazer INDEX MATCH com vários critérios no Excel Tutorial.

Pesquisas em várias colunas

Outro uso excelente do XMATCH é a combinação de vários critérios para criar pesquisas em várias colunas. Isso pode não parecer intuitivo, pois o XMATCH look requer um valor de pesquisa e uma matriz de pesquisa para funcionar. Mas podemos usar a lógica booleana para criar uma matriz temporária dentro da fórmula XMATCH que contém zeros e uns para representar as linhas que correspondem aos critérios que você especificar. 

Por exemplo, digamos que você queira encontrar a primeira tecnologia da lista que tenha menos de 1.000 usuários inscritos a cada trimestre. Você pode combinar as condições combinando os resultados em cada coluna usando o símbolo *. O Excel encontrará sob o capô a matriz que contém todos os uns, no nosso caso, o Tableau, por isso a posição 5.

Filtragem de matriz dinâmica

O XMATCH também pode trabalhar em conjunto com as funções FILTER e ISNUMBER para aprimorar a filtragem de matriz dinâmica.

Abaixo, você pode descobrir como ele funciona. Usaremos essas funções para filtrar as informações dos cursos concluídos pelos usuários com base no nome de usuário. 

Primeiro, usamos o XMATCH para calcular a posição relativa de cada usuário na tabela com base na matriz de pesquisa com os nomes que usaremos para filtrar. Isso nos dará uma tabela com a posição relativa de cada nome, incluindo valores nulos para usuários não incluídos na lista de filtros.

Em seguida, convertemos a coluna com posições relativas e valores nulos para TRUE ou FALSE usando a função ISNUMBER.

Por fim, usamos FILTER para selecionar todas as colunas que queremos exibir e a coluna com TRUE e FALSE para filtrar as linhas.

Abordagem de valores nulos com a ISNA

Quando o XMATCH não encontrar nenhuma correspondência, ele retornará um erro NA. Embora isso já seja informativo, você pode embelezar os resultados e evitar interpretações incorretas usando as funções ISNA e IF.

Imagine que temos uma tabela com os cursos concluídos por Sam e Peter, e queremos saber quais cursos eles têm em comum. Se usarmos apenas o XMATCH, o Excel retornará a posição relativa dos cursos comuns e, caso contrário, valores nulos. Se passarmos XMATCH como argumento de ISNA, a função retornará TRUE ou FALSE. Em seguida, usando a função IF, podemos alterar os valores booleanos de outras mensagens.

Pesquisa binária para grandes conjuntos de dados

Já mencionamos que o XMATCH permite que você use um algoritmo de pesquisa binária durante as operações de correspondência. Esse recurso pode ser um divisor de águas ao lidar com grandes conjuntos de dados. Desde que a matriz de pesquisa esteja classificada, a opção pelo mecanismo de pesquisa binária pode acelerar drasticamente suas operações de correspondência.

XMATCH vs MATCH: Principais diferenças

Como já mencionado, o XMATCH foi projetado para substituir a função MATCH tradicional. Ambos têm a mesma finalidade, mas o XMATCH vem com um conjunto de novos recursos, o que o torna mais avançado que o MATCH. Abaixo, você encontrará uma lista das principais diferenças entre XMATCH e MATCH:

Armadilhas e soluções comuns

Há muitos usos potenciais do XMATCH. No entanto, é importante mencionar os problemas comuns que você pode encontrar ao usá-lo. Aqui está uma lista de algumas armadilhas comuns e dicas para solucioná-las.

• Verifique os tipos de dados: O XMATCH terá um comportamento inesperado se você o aplicar a um intervalo com o tipo de dados errado. Portanto, antes de aplicar a função, verifique se você definiu os tipos de dados corretos para suas colunas.
• Consistência da faixa: Embora o XMATCH permita intervalos de pesquisa horizontais e verticais, misturá-los pode levar a problemas inesperados e problemas de desempenho. Quando possível, tente ser consistente e escolha faixas horizontais ou verticais.
• Aproveite os modos de correspondência: O XMATCH vem com vários modos de partida. O modo padrão é a correspondência exata, mas você também pode ajustar suas pesquisas usando buscas aproximadas e operações de regex.
• Melhor com matrizes ordenadas: Em geral, o desempenho do XMATCH sempre será maior se você aplicá-lo a matrizes classificadas. Isso é verdadeiro para a função padrão e é um pré-requisito obrigatório se você quiser usar a pesquisa binária. 
• Manipulação de erros N/A:  Como já foi mencionado, o XMATCH lançará um erro NA se não houver correspondência em suas pesquisas. A melhor maneira de resolver isso e melhorar a legibilidade é passar os resultados para uma combinação de funções IF E ISNA ou para uma função IFNA.

Considerações sobre o desempenho

O XMATCH não é a única função para realizar operações de pesquisa no Excel. Por exemplo, você pode considerar o uso de VLOOKUP, HLOOKUPou até mesmo funções legadas como MATCH. No entanto, quando se trata de velocidade de pesquisa, o XMATCH é indiscutivelmente o mais rápido, especialmente se você aplicá-lo a matrizes ordenadas, onde também pode aproveitar o poder da pesquisa binária.

Também vale a pena mencionar que o XMATCH foi projetado como uma fórmula de matriz dinâmica, o que significa que ele pode retornar matrizes de tamanho variável, dependendo dos dados de entrada. As fórmulas de matriz dinâmica são especialmente adequadas quando você trabalha com grandes conjuntos de dados ou cálculos complexos. 

Conclusão

Parabéns por você ter chegado até o fim. O XMATCH é uma função poderosa, e você não pode perdê-la em suas análises de dados do Excel. Seja como uma função autônoma ou em conjunto com outras funções do Excel, o XMATCH levará suas operações de pesquisa a um novo patamar. 

Mas há muito mais para você conhecer no maravilhoso mundo do Excel. Confira nossos cursos, blogs e tutoriais dedicados para que você se torne um mago do Excel:

• XLOOKUP() vs. VLOOKUP(): Uma comparação para usuários do Excel
• Manipulação de dados na folha de dicas do Excel 
• Curso de Excel | Master Advanced Techniques
• Power Query intermediário no Excel 
• Atalhos do Excel 
• Planilha de fórmulas do Excel 
• Como fazer uma correspondência de índice com vários critérios no Excel 
• Análise de dados com INDEX-MATCH em planilhas 
• Tutorial de Regex do Excel: Dominando a correspondência de padrões com expressões regulares