Como usar INDEX e XMATCH no Excel para buscas bidirecionais

Links rápidos

• Como as funções INDEX e XMATCH funcionam no Excel

• Usando INDEX com XMATCH para pesquisas unidirecionais

• Usando INDEX com XMATCH para pesquisas bidirecionais

Para muitas pessoas, a combinação INDEX + XMATCH no Excel é o método preferido para recuperar um valor de um conjunto de dados. Além de buscas simples (uma dimensão), essa dupla também pode realizar buscas bidirecionais, retornando o valor na interseção de uma linha e uma coluna especificadas.

Este artigo atende leitores de todos os níveis, aumentando a complexidade gradualmente. Se você ainda não conhece INDEX e XMATCH, comece pelo início. Se já usa INDEX + XMATCH para buscas simples, vá direto para “Usando INDEX com XMATCH para buscas bidirecionais”.

Como as funções INDEX e XMATCH funcionam

Antes de ver como usar as duas em uma única fórmula, vamos entender cada função isoladamente.

A função INDEX

A função INDEX retorna um valor dentro de um intervalo com base no número da linha e da coluna que você especifica. Sintaxe:

=INDEX(a,b,c)

onde

• a é o intervalo (ou tabela),
• b é o número da linha dentro desse intervalo, e
• c é o número da coluna dentro desse intervalo.

Exemplo técnico: se você digitar:

=INDEX(T_Profit,3,6)

o Excel retorna o valor que está na 3ª linha e na 6ª coluna do intervalo (ou tabela) T_Profit.

Você também pode referenciar células que contenham os números da linha e da coluna em vez de codificá-los, o que torna a fórmula dinâmica. Por exemplo:

=INDEX(T_Profit,I2,I3)

puxa o número da linha de I2 e o número da coluna de I3.

Definição rápida: INDEX = “ir para a linha X e coluna Y dentro do intervalo A e devolver o valor”.

A função XMATCH

XMATCH pesquisa um item em um intervalo e retorna a posição do item dentro desse intervalo. Sintaxe:

=XMATCH(a,b,c,d)

onde

• a é o item a procurar,
• b é o intervalo onde procurar,
• c é o tipo de correspondência (0 = correspondência exata (padrão); -1 = exata ou próximo menor; 1 = exata ou próximo maior; 2 = correspondência com curinga), e
• d é o modo de busca (1 = do primeiro ao último (padrão), -1 = do último ao primeiro, 2 = busca binária com b em ordem crescente, -2 = busca binária com b em ordem decrescente).

Observação de compatibilidade: XMATCH está disponível no Excel 2021 e posteriores, no Microsoft 365, no Excel para a web e nas apps móveis. Usuários de versões antigas devem usar MATCH (com limitações) ou atualizar.

Exemplo simples:

=XMATCH(1927,T_Profit[Employee])

se retornar 3, significa que o valor 1927 é o terceiro item na coluna Employee do T_Profit.

Você pode referenciar uma célula para tornar a busca dinâmica:

=XMATCH(I2,T_Profit[Employee])

Definição rápida: XMATCH = “encontre a posição de X dentro do intervalo B”.

Importante: XMATCH favorece correspondência exata por padrão, ao contrário de MATCH (no qual os argumentos padrão podiam provocar correspondências aproximadas inesperadas).

Usando INDEX com XMATCH para buscas unidirecionais

A combinação torna a busca mais robusta: XMATCH encontra a linha (ou coluna) e INDEX retorna o valor com base nessa posição.

Exemplo prático: você quer ver o lucro total de um empregado ao digitar o ID dele em I2.

Fórmula em I5:

=INDEX(T_Profit,XMATCH(I2,T_Profit[Employee]),6)

onde

• T_Profit é a tabela onde está o dado a ser recuperado,
• XMATCH(I2,T_Profit[Employee]) encontra a linha correspondente ao ID em I2,
• 6 é a coluna (por número) que contém o lucro total.

Dica rápida: ao usar tabelas estruturadas, clique no cabeçalho da coluna para inserir referências estruturadas automaticamente em vez de digitar manualmente.

Usando INDEX com XMATCH para buscas bidirecionais

A vantagem das buscas bidirecionais é permitir que tanto a linha quanto a coluna sejam determinadas por valores em células — sem editar a fórmula quando você mudar os parâmetros.

Exemplo: descobrir quanto o empregado 1191 ganhou em 2021.

Fórmula:

=INDEX(T_Profit,XMATCH(I2,T_Profit[Employee]),XMATCH(I3,T_Profit[#Headers]))

onde

• XMATCH(I2,T_Profit[Employee]) encontra a linha para o empregado,
• XMATCH(I3,T_Profit[#Headers]) encontra a coluna correspondente ao cabeçalho (por exemplo, “2021”).

Problema comum (erros #N/A): cabeçalhos numéricos armazenados como texto

• Causa: Excel armazena cabeçalhos de coluna de tabela como texto, mesmo que pareçam números (por exemplo, anos).
• Efeito: XMATCH pode não encontrar “2021” se um lado for texto e o outro número.

Soluções práticas:

1. Forçar o formato de texto na célula de busca (I3): Selecionar I3 → guia Início → Formato de Número → Texto. Em seguida, editar a célula (F2) e Enter para forçar o Excel a tratar o valor como texto.

2. Converter cabeçalhos em números ou texto de forma consistente:

   • Converter cabeçalhos para números: use VALUE() ao criar a referência, por exemplo,

=XMATCH(VALUE(I3),VALUE(T_Profit[#Headers]))

• Converter o valor de busca para texto: use TEXT()

=XMATCH(TEXT(I3,"0"),T_Profit[#Headers])

3. Normalizar com uma coluna auxiliar: crie uma linha de cabeçalhos normalizados (todos texto ou todos números) e use essa linha para a busca.

Nota: escolha a abordagem que mantém seus dados consistentes a longo prazo.

Depois de corrigir o tipo, a fórmula retorna o resultado esperado:

E você pode trocar os parâmetros em I2 e I3 para ver outros resultados dinamicamente:

Boas práticas e alternativas

• Prefira tabelas estruturadas (Inserir > Tabela): referências ficam mais claras, o intervalo se ajusta automaticamente e fórmulas em tabelas podem ser mais legíveis.
• Use nomes de intervalo se você reutiliza o mesmo conjunto de colunas em várias fórmulas.
• Consistência de tipos: sempre normalize números vs texto em cabeçalhos e valores de busca.

Alternativas quando INDEX+XMATCH não for a melhor escolha:

• XLOOKUP (mais direto em muitas buscas): pode buscar em linha ou coluna e retornar um valor correspondente sem combinar duas funções. Exemplo:

=XLOOKUP(I2,T_Profit[Employee],T_Profit[Profit2021])

• FILTER (para retornar múltiplos resultados ou várias colunas): retorna um conjunto de linhas que atendem a uma condição.

• VLOOKUP/HLOOKUP: soluções legadas, menos flexíveis e mais propensas a erros quando colunas são inseridas/deslocadas.

Contraponto/Quando não usar INDEX+XMATCH:

• Se você precisa de uma única busca simples e sua versão do Excel suporta XLOOKUP, XLOOKUP pode ser mais direto.
• Para conjuntos de dados muito grandes, medidas de desempenho (como cálculos voláteis, uso de busca binária) devem ser consideradas.

Data validation e menus suspensos para os parâmetros de busca

Você pode criar listas suspensas para I2 (IDs) e I3 (anos/cabeçalhos) com Validação de Dados, acelerando a entrada e evitando erros.

Limitação: não é possível apontar diretamente para T_Profit[#Headers] no campo Fonte da Validação. Soluções:

• Use referências diretas de células: selecione o intervalo físico das células com os cabeçalhos (por exemplo, $B$1:$G$1) no campo Fonte.
• Nomeie o intervalo de cabeçalhos (Fórmulas > Gerenciador de Nomes) e use esse nome na Fonte.
• Crie uma lista auxiliar em outro local com os valores que deseja exibir e referencie essa lista.

Dica prática: para listas que cambiam de tamanho, use uma referência dinâmica (por exemplo, usando INDEX para definir o final do intervalo) ou crie uma tabela com os cabeçalhos e referencie a coluna dessa tabela.

Mini-metodologia: passo a passo para implementar uma busca bidirecional robusta

1. Organize os dados em uma tabela estruturada (Inserir > Tabela).
2. Verifique e normalize tipos de dados em cabeçalhos e colunas (texto vs número).
3. Crie células de entrada (ex.: I2 para ID, I3 para Ano) e formate-as adequadamente.
4. Se desejar, crie listas suspensas para I2 e I3 usando Validação de Dados e nomes de intervalo.
5. Insira a fórmula:

=INDEX(T_Profit,XMATCH(I2,T_Profit[Employee]),XMATCH(I3,T_Profit[#Headers]))

6. Teste com vários casos (existente, inexistente, tipos mistos) e trate erros com IFERROR ou LET, se necessário.

Exemplo com tratamento de erro e LET para legibilidade (Excel 365/2021):

=LET(
  tbl, T_Profit,
  lin, XMATCH(I2, tbl[Employee], 0, 1),
  col, XMATCH(I3, tbl[#Headers], 0, 1),
  IFERROR(INDEX(tbl, lin, col), "Valor não encontrado")
)

Tipos de teste e critérios de aceitação

Testes básicos:

• Caso positivo: ID e cabeçalho existem → fórmula devolve valor correto.
• Caso negativo (ID inexistente): fórmula trata o erro com mensagem amigável.
• Caso negativo (cabeçalho inexistente): mesma verificação.
• Tipo misto: cabeçalho numérico vs texto → confirma normalização funciona.
• Mudança de tamanho da tabela: inserir nova coluna/linha → fórmula continua válida.

Critérios de aceitação (resumidos):

• Retorna valores corretos para vários inputs válidos.
• Não quebra quando novas colunas são adicionadas à tabela.
• Fornece mensagem controlada quando não encontra valor.

Checklists por função

Analista:

• Tabela estruturada criada
• Nomes de colunas validados e normalizados
• Fórmula INDEX+XMATCH inserida e testada
• Validação de dados para entradas implementada

Gerente:

• Relatório demonstra valores corretos por amostragem
• Tratamento de erro e mensagens explicativas presentes

Desenvolvedor/Automação:

• Usar LET para clareza e performance quando possível
• Evitar referências voláteis desnecessárias
• Documentar nomes de intervalos e dependências

Mapa de decisão (Mermaid)

flowchart TD
  A[Começar: preciso buscar valor por linha e coluna?] --> B{Tenho XLOOKUP disponível?}
  B -- Sim --> C[Considere XLOOKUP ou XLOOKUP + XMATCH]
  B -- Não --> D{Precisa de busca bidirecional?}
  D -- Sim --> E[Usar INDEX + XMATCH]
  D -- Não --> F[Usar XMATCH para posição + INDEX ou FILTER]
  C --> G[Fim]
  E --> G
  F --> G

Casos de borda e mitigação de riscos

• Colunas com nomes duplicados: renomeie cabeçalhos para garantir unicidade.
• Valores repetidos (IDs duplicados): XMATCH retorna a primeira ocorrência por padrão; se precisar da última, use o argumento de pesquisa -1 (busca do fim) ou trate duplicatas na modelagem dos dados.
• Dados externos atualizados frequentemente: prefira tabelas e nomes dinâmicos para evitar referências quebradas.

Notas de compatibilidade e migração

• XMATCH requer Excel 2021 / Microsoft 365 / Excel web / apps móveis. Para usuários de Excel 2019 ou anterior, substitua XMATCH por MATCH quando possível (atenção aos argumentos), ou considere atualizar.
• LET, funções de matriz dinâmica e outras funcionalidades recentes também podem não estar disponíveis em versões antigas.

Glossário rápido (uma linha cada)

• INDEX: retorna um valor com base em um número de linha e coluna.
• XMATCH: encontra a posição de um item dentro de um intervalo.
• Tabela estruturada: intervalo nomeado que cresce automaticamente quando você adiciona linhas/colunas.

Exemplo final com melhorias e tratamentos

Fórmula completa recomendada para Excel 365/2021 com validação de erro e legibilidade:

=LET(
  tbl, T_Profit,
  idPos, XMATCH(I2, tbl[Employee], 0, 1),
  colPos, XMATCH(TEXT(I3,"@"), tbl[#Headers], 0, 1),
  resultado, INDEX(tbl, idPos, colPos),
  IFERROR(resultado, "Não encontrado")
)

Notas:

• TEXT(I3,”@”) força I3 para texto antes da busca nos cabeçalhos.
• IFERROR evita mensagens de erro pouco amigáveis.

Resumo rápido

Importante: INDEX + XMATCH é uma solução flexível para buscas unidirecionais e bidirecionais. Normalizar tipos, usar tabelas estruturadas e implementar Validação de Dados tornam a solução robusta e fácil de usar.

Resumo dos passos:

• Estruture os dados em tabela;
• Normalize tipos (texto vs número);
• Use XMATCH para obter posições e INDEX para recuperar o valor;
• Trate erros e implemente validação de dados para evitar entradas inválidas.