M365Con 2027 – conferência online gratuita · 11–22 Jan 2027 · 120 sessions m365con.net

Blog · Mirko Peters

INDEX MATCH no Excel: fórmula, exemplos e erros comuns

By · · Updated

INDEX MATCH no Excel explicado: a fórmula, como cada função funciona, pesquisas à esquerda e bidirecionais, múltiplos critérios e erros comuns, além de uma comparação com PROCV.

Automatically translated with AI from the original post. Read the English original

O INDEX MATCH no Excel é uma fórmula de pesquisa formada por duas funções: =INDEX(intervalo_retorno; CORRESP(valor_pesquisado; intervalo_pesquisa; 0)). O CORRESP encontra onde o seu valor está em uma lista, e o INDEX retorna o item da mesma posição em outra lista. Este artigo é para usuários do Excel que escrevem fórmulas PROCV e esbarram nas suas limitações, ou que herdaram uma pasta de trabalho cheia de INDEX MATCH e querem entendê-la. Você verá cada função isoladamente, como elas se encaixam, como pesquisar à esquerda, como fazer uma pesquisa bidirecional, como usar dois critérios e o que significam os erros.

Pontos-chave

  • O INDEX MATCH são duas funções em uma única fórmula: =INDEX(intervalo_retorno; CORRESP(valor_pesquisado; intervalo_pesquisa; 0)). O CORRESP encontra a posição e o INDEX retorna o item nessa posição.
  • Use 0 como terceiro argumento do CORRESP para uma correspondência exata. Deixado no padrão 1, o CORRESP presume que os dados estão classificados em ordem crescente.
  • Como os intervalos de pesquisa e de retorno são separados, o INDEX MATCH pode pesquisar à esquerda da coluna de pesquisa, o que o PROCV não consegue fazer.
  • Para duas condições, você pode juntar as colunas em uma coluna auxiliar ou usar uma fórmula que multiplique duas comparações dentro do CORRESP.
  • Envolva a fórmula em SEERRO.NA para substituir #N/D por uma mensagem, mantendo a exibição de outros erros, para que erros reais não fiquem ocultos.

Você encontrará o INDEX MATCH em muitas pastas de trabalho antigas, porque ele funcionava em todas as versões do Excel muito antes de o PROCX existir. Ele continua funcionando, e esse é o principal motivo para aprendê-lo. Os detalhes a seguir vêm das páginas da Microsoft sobre CORRESP, ÍNDICE e PROCV.

O que o INDEX faz

O INDEX retorna um valor a partir de uma posição dentro de um intervalo. A página da Microsoft apresenta a forma de matriz como INDEX(matriz; núm_linha; [núm_coluna]).

  • matriz é o intervalo do qual o valor será retirado.
  • núm_linha é a linha dentro desse intervalo.
  • núm_coluna é a coluna dentro desse intervalo. Se o intervalo tiver apenas uma coluna, você pode omiti-lo.

Os números são contados a partir da célula superior esquerda do intervalo, não da planilha. O exemplo da Microsoft usa um bloco com Maçãs e Bananas na primeira coluna e Limões e Peras na segunda. =INDEX(A2:B3;2;2) retorna Peras, e =INDEX(A2:B3;2;1) retorna Bananas.

Duas regras da mesma página merecem ser lembradas. Se núm_linha ou núm_coluna apontar para fora do intervalo, o INDEX retorna um erro #REF!. E se você definir núm_linha ou núm_coluna como 0, o INDEX retorna a coluna ou a linha inteira. No Microsoft 365 atual, você pressiona Enter normalmente. Em versões mais antigas, você insere a fórmula como fórmula de matriz com Ctrl+Shift+Enter.

Sozinho, o INDEX exige que você já conheça a posição. É aí que entra o CORRESP.

O que o MATCH faz

O CORRESP pesquisa um intervalo e retorna uma posição. A Microsoft apresenta a sintaxe como CORRESP(valor_pesquisado; matriz_pesquisada; [tipo_correspondência]). Ele não retorna o item em si, apenas a posição relativa dele no intervalo.

O terceiro argumento determina como a correspondência é feita:

  • 0 encontra a primeira correspondência exata. O intervalo pode estar em qualquer ordem. Este é o que você vai usar na maioria das vezes.
  • 1, que também é o padrão caso você omita o argumento, encontra o maior valor menor ou igual ao valor pesquisado. O intervalo precisa estar classificado em ordem crescente.
  • -1 encontra o menor valor maior ou igual ao valor pesquisado. O intervalo precisa estar classificado em ordem decrescente.

Os exemplos da Microsoft usam os valores 25, 38, 40 e 41 em B2:B5. =MATCH(39,B2:B5,1) retorna 2, a posição do 38, que é o próximo valor mais baixo. =MATCH(41,B2:B5,0) retorna 4. E =MATCH(40,B2:B5,-1) retorna #N/D, porque esses valores não estão em ordem decrescente.

Há mais alguns comportamentos descritos na página: a correspondência de texto ignora maiúsculas e minúsculas, então "apple" e "APPLE" são iguais. Com tipo de correspondência 0 e um valor de pesquisa em texto, ? corresponde a qualquer caractere único e * corresponde a qualquer sequência de caracteres, e um til antes de qualquer um dos dois faz a busca pelo caractere literal. Se o CORRESP não encontrar nada, ele retorna #N/D.

O padrão de 1 costuma causar problemas. Se você esquecer o terceiro argumento, o CORRESP assume silenciosamente que os dados estão classificados e pode retornar uma posição errada sem nenhum erro. Em quase toda pesquisa de IDs, nomes ou códigos, digite 0.

Juntando tudo

O padrão é sempre o mesmo: CORRESP fica dentro de ÍNDICE e fornece o número da linha.

=INDEX(intervalo_retorno, MATCH(valor_pesquisado, intervalo_pesquisa, 0))

Imagine uma planilha com IDs de funcionários em A2:A100, nomes em B2:B100 e departamentos em C2:C100. Você digita um ID em F2 e quer o departamento.

  1. Clique na célula do resultado, por exemplo G2.
  2. Digite =INDEX(C2:C100, para indicar de onde vem a resposta.
  3. Adicione MATCH(F2,A2:A100,0)) para indicar onde encontrar o ID.
  4. Pressione Enter.

A fórmula final é =INDEX(C2:C100,MATCH(F2,A2:A100,0)). O CORRESP encontra F2 em A2:A100 e retorna, por exemplo, 14. O ÍNDICE então retorna o 14º item em C2:C100.

Mantenha os intervalos com o mesmo tamanho e a mesma linha inicial. Se o CORRESP pesquisar A2:A100 e o ÍNDICE ler C3:C101, a posição aponta para a linha errada e a resposta fica silenciosamente incorreta. Use as mesmas linhas inicial e final para os dois, e fixe-as com cifrões, $A$2:$A$100, se você planeja copiar a fórmula para baixo.

Pesquisando à esquerda e o que muda em relação ao PROCV

A página do PROCV da Microsoft apresenta a sintaxe como VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Ela diz que o valor pesquisado precisa estar na primeira coluna do intervalo e que omitir range_lookup resulta em uma correspondência aproximada, que pressupõe dados classificados. A página também sugere experimentar o PROCX.

Aqui está a mesma tarefa feita das duas formas, com IDs na coluna A e nomes na coluna B:

  • =VLOOKUP(F2,A2:B100,2,FALSE)
  • =INDEX(B2:B100,MATCH(F2,A2:A100,0))

O ÍNDICE/CORRESP tem duas vantagens reais. Primeiro, ele consegue pesquisar à esquerda. Se os nomes estiverem na coluna B e você quiser o ID de volta da coluna A, basta trocar os intervalos: =INDEX(A2:A100,MATCH(F2,B2:B100,0)). O PROCV não consegue fazer isso, porque o valor pesquisado precisa estar na primeira coluna. Segundo, ele aponta diretamente para a coluna de retorno, em vez de contar. No PROCV, o 2 da fórmula significa "segunda coluna do intervalo". Se você inserir uma coluna dentro do intervalo, o número passa a apontar para outra coluna. Com ÍNDICE/CORRESP, a referência se ajusta como qualquer outra referência.

Você também vai ler que o ÍNDICE/CORRESP é mais rápido. As páginas da Microsoft não fazem essa afirmação, e qualquer diferença só importa em pastas de trabalho muito grandes, então não escolha por velocidade. Escolha pela flexibilidade.

Pesquisas bidirecionais

Uma pesquisa bidirecional encontra o valor onde uma linha e uma coluna se cruzam, por exemplo as vendas de um produto em um mês. Use duas funções CORRESP, uma para a linha e outra para a coluna:

=INDEX(B2:M20,MATCH(P1,A2:A20,0),MATCH(Q1,B1:M1,0))

Aqui, B2:M20 contém os números, A2:A20 tem os nomes dos produtos, B1:M1 tem os cabeçalhos dos meses, e P1 e Q1 guardam o produto e o mês que você quer. O primeiro MATCH retorna a linha, o segundo retorna a coluna, e o INDEX devolve o número onde elas se cruzam. Observe que o intervalo de retorno começa em B2, e não em A1, para que as posições coincidam com os intervalos de rótulos: os dois intervalos do MATCH também começam na mesma linha ou coluna dos números.

Correspondência com dois critérios

O MATCH aceita apenas um valor de pesquisa. Se um registro é identificado por dois campos, como categoria e status, você tem duas opções.

Uma coluna auxiliar. Adicione uma coluna que una os dois campos, por exemplo =A2&"|"&B2 em D2, e preencha para baixo. Depois, pesquise o texto unido:

=INDEX(C2:C100,MATCH(F2&"|"&G2,D2:D100,0))

O separador é importante. Sem ele, "AB" mais "C" e "A" mais "BC" resultam ambos em "ABC". A coluna auxiliar é fácil de ler e de verificar, e funciona em todas as versões.

Uma fórmula de matriz. Sem coluna auxiliar, você pode multiplicar duas comparações:

=INDEX(C2:C100,MATCH(1,(A2:A100=F2)*(B2:B100=G2),0))

Cada comparação resulta em VERDADEIRO ou FALSO. A multiplicação os transforma em 1 apenas onde ambas são verdadeiras, e o MATCH procura o primeiro 1. Isso não aparece nas páginas de INDEX e MATCH da Microsoft, então teste em um intervalo pequeno. Em versões mais antigas do Excel, talvez seja preciso inseri-la com Ctrl+Shift+Enter, da mesma forma que a Microsoft descreve para resultados de matriz do INDEX. No Microsoft 365 atual, basta Enter.

Curingas e valores mais próximos

Com tipo de correspondência 0 e texto, o MATCH aceita curingas. =INDEX(B2:B100,MATCH("Jan*",A2:A100,0)) retorna o item ao lado da primeira entrada que começa com "Jan". Isso é útil para códigos parciais. Mantenha o padrão específico, porque o MATCH para na primeira ocorrência.

Para faixas, como faixas de imposto ou de comissão, use de propósito o tipo de correspondência padrão 1, com os limites em ordem crescente. Coloque 0, 1000 e 5000 em A2:A4 e as taxas em B2:B4, e =INDEX(B2:B4,MATCH(E2,A2:A4,1)) retorna a taxa do maior limite que não ultrapassa E2. Esta é uma das poucas situações em que o padrão com dados ordenados é a escolha certa.

Corrigindo os erros

  • #N/A. O MATCH não encontrou nada. Procure erros de digitação, espaços no final e números armazenados como texto, já que "100" e 100 são diferentes. Verifique se o tipo de correspondência é 0 e se os tipos de correspondência com dados ordenados (1 ou -1) realmente têm dados ordenados.
  • #REF!. O INDEX pediu uma linha ou coluna fora do intervalo. Isso também acontece quando você exclui uma linha ou coluna que a fórmula usa. Verifique se o intervalo do MATCH e o intervalo do INDEX têm o mesmo tamanho.
  • Uma resposta errada sem nenhum erro. Geralmente é um 0 ausente no MATCH, ou intervalos que começam em linhas diferentes.
  • Uma resposta errada depois de copiar para baixo. Os intervalos se moveram porque não estavam travados. Adicione $ aos intervalos.

Para substituir um erro por uma mensagem, a função SEERRO.NA da Microsoft retorna um valor escolhido por você quando a fórmula gera #N/A, e deixa passar qualquer outro erro sem alterações:

=SEERRO.NA(INDEX(C2:C100,MATCH(F2,A2:A100,0)),"Não encontrado")

Prefira SEERRO.NA a SEERRO em pesquisas. O SEERRO oculta todos os erros, incluindo um #REF! causado por um intervalo quebrado, então uma falha real parece um simples "Não encontrado". O SEERRO.NA cobre apenas o caso que você espera. A Microsoft indica o SEERRO.NA para o Excel 2016 e versões posteriores.

INDEX MATCH ou XLOOKUP

Se todos que abrem seu arquivo têm o Microsoft 365 atualizado, o PROCX faz o mesmo trabalho com uma única função e uma mensagem de não encontrado integrada. Abordamos isso no nosso guia sobre PROCX no Excel. Mas a própria página do PROCX da Microsoft não é clara sobre o Excel 2016 e 2019, e não lista o Excel para a web. O INDEX MATCH funciona em todos eles. Então a escolha é prática: use o PROCX em arquivos para versões atuais, e o INDEX MATCH quando o arquivo for para pessoas com versões mais antigas ou quando você estiver mantendo uma pasta de trabalho que já o utiliza.

Erros comuns

  • Esquecer o 0 no MATCH e confiar no padrão.
  • Usar intervalos de tamanhos diferentes ou com linhas iniciais diferentes para INDEX e MATCH.
  • Esquecer de travar os intervalos com $ antes de copiar a fórmula para baixo.
  • Unir critérios sem separador em uma coluna auxiliar.
  • Envolver tudo em SEERRO, de modo que fórmulas quebradas pareçam dados ausentes.
  • Você espera que o INDEX MATCH retorne todas as correspondências. Ele retorna apenas a primeira.

Tópicos relacionados para explorar

  • XLOOKUP. A função de pesquisa mais recente, com direção de busca e argumento para valor não encontrado já integrados.
  • Vários resultados de uma vez. O INDEX MATCH retorna apenas a primeira correspondência. A função FILTER é a ferramenta para retornar todas as correspondências nas versões que a possuem.
  • Tabelas e intervalos nomeados. Referências estruturadas e nomes deixam as pesquisas mais fáceis de ler e permitem que o intervalo cresça junto com seus dados.
  • Desempenho em planilhas grandes. Use intervalos delimitados em vez de colunas inteiras e mantenha os intervalos enxutos.
  • Pesquisas sensíveis a maiúsculas e minúsculas. O MATCH ignora diferenças de maiúsculas e minúsculas, então uma pesquisa que diferencie essas letras exige outra abordagem. Teste qualquer solução alternativa antes de depender dela.

Próximo passo

Reescreva uma de suas fórmulas PROCV como INDEX MATCH e compare o resultado em alguns valores conhecidos. Em seguida, teste a mesma fórmula com uma pesquisa à esquerda. Depois de fazer as duas coisas, você saberá quando o INDEX MATCH no Excel é a ferramenta mais adequada para a tarefa.

Fontes

Perguntas frequentes

Como o INDEX MATCH funciona no Excel?

O MATCH procura o valor de pesquisa em um intervalo e retorna a posição dele, por exemplo 5 para o quinto item. Em seguida, o INDEX usa esse número para retornar o item na mesma posição em outro intervalo. Juntos, =INDEX(C2:C100;CORRESP(F2;A2:A100;0)) localiza F2 na coluna A e retorna o valor da coluna C.

O INDEX MATCH é melhor que o PROCV?

É mais flexível. O PROCV exige que o valor de pesquisa esteja na primeira coluna do intervalo e conta as colunas por número, enquanto o INDEX MATCH usa dois intervalos separados, por isso pode pesquisar à esquerda e não é afetado por colunas inseridas. Para uma pesquisa simples à direita, qualquer um dos dois funciona. O PROCX é uma terceira opção nas versões mais recentes.

Por que o INDEX MATCH retorna #N/D?

O CORRESP retorna #N/D quando não encontra correspondência. Verifique erros de digitação, espaços extras, números armazenados como texto e o tipo de correspondência. Use 0 para correspondência exata. Com o padrão 1, o intervalo de pesquisa precisa estar classificado em ordem crescente, e com -1 precisa estar classificado em ordem decrescente.

Entities in this article

Temas deste artigo:

Microsoft Excel – Ferramenta de análise e BI da Microsoft

Related articles

Mais artigos sobre Microsoft Excel:

RSS