INDEX MATCH no Excel: fórmula, exemplos e erros comuns
By Mirko Peters · · 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:
0encontra 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.-1encontra 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.
- Clique na célula do resultado, por exemplo G2.
- Digite
=INDEX(C2:C100,para indicar de onde vem a resposta. - Adicione
MATCH(F2,A2:A100,0))para indicar onde encontrar o ID. - 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
0no 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
- Função CORRESP (Microsoft Support)
- Função ÍNDICE (Microsoft Support)
- Função PROCV (Microsoft Support)
- Função SEERRO... (Microsoft Support)
- Função PROCX (Microsoft Support)
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.