Correspondência de índice no Excel: Uma maneira melhor de procurar dados

INDEX MATCH, uma combinação das duas funções do Excel, INDEX() e MATCH(), é uma alternativa à função VLOOKUP(), que é bem usada.

Neste guia, vou te mostrar como o INDEX() e o MATCH() funcionam sozinhos e juntos, como fazer pesquisas verticais e horizontais e as melhores práticas para usar o INDEX MATCH com exemplos práticos.

O que é INDEX MATCH no Excel?

INDEX MATCH é uma forma rápida de falar sobre como as duas funções estão dentro uma da outra. É tipo a ideia de INDEX(MATCH()). Vamos ver como os dois funcionam sozinhos e juntos.

Função INDEX() no Excel

A função INDEX() no Excel mostra um valor ou uma referência a um valor dentro de um intervalo ou tabela. Você pode usar isso de duas maneiras: na forma de matriz e na forma de referência.

Formulário de matriz

Você pode usar o formato de matriz quando estiver trabalhando com uma tabela ou matriz fixa. Nesse formato, INDEX() pega um valor com base no número da linha e coluna que você escolher. A sintaxe é:

INDEX(array, row_num, [column_num])

Vamos entender esses argumentos:

• array (obrigatório) é o intervalo ou a matriz de valores com os quais você está trabalhando.

• row_num (obrigatório) é o número da linha da qual você quer pegar um valor.

• column_num (opcional) é o número da coluna da qual você quer pegar um valor.

Precisamos colocar pelo menos um, seja row_num ou column_num, pra que o Excel consiga achar o valor.

Mas, aqui vão algumas dicas rápidas que você deve lembrar ao usar essa sintaxe:

• Se a matriz tiver só uma linha ou coluna, dá pra pular row_num ou column_num.

• Se tanto row_num quanto column_num estiverem incluídos, INDEX vai mostrar o valor onde eles se encontram.

• Se você definir row_num ou column_num como 0, o Excel vai mostrar a coluna ou linha inteira (você vai precisar colocar isso como uma fórmula de matriz pra ver o resultado).

Formulário de referência

Você pode usar o formulário de referência quando estiver trabalhando com vários intervalos e precisar especificar qual deles deve ser analisado.

A sintaxe é:

INDEX(reference, row_num, [column_num], [area_num])

Vamos entender esses argumentos:

• reference (obrigatório) é usado pra referenciar uma ou mais áreas de células.

• row_num (obrigatório) é o número da linha na área selecionada.

• column_num (opcional) é o número da coluna na área selecionada.

• area_num (opcional) diz ao Excel qual intervalo usar se tiver mais de um.

Aqui estão alguns pontos adicionais que você precisa lembrar:

• Se você não colocar area_num, o Excel vai usar o primeiro intervalo por padrão.

• Não dá pra misturar intervalos de planilhas diferentes nesse formulário; isso vai dar erro.

• Se tanto a linha quanto a coluna forem omitidas, ele mostra toda a área especificada.

Função MATCH() no Excel

O MATCH() função ajuda a encontrar a posição de um valor em uma linha ou coluna. Em vez de devolver o valor em si, ele diz onde ele está na lista.

A sintaxe é:

MATCH(lookup_value, lookup_array, [match_type])

Vamos entender esses argumentos:

• lookup_value (obrigatório) é o valor que você quer encontrar. Pode ser um número, texto, valor lógico (TRUE/FALSE) ou uma referência a uma célula.

• lookup_array (obrigatório) é o intervalo de células que o Excel vai procurar.

• match_type (opcional) define como o Excel procura a correspondência. O padrão é 1. Aqui, 1 encontra o maior valor menor ou igual a lookup_value (precisa estar em ordem crescente). 0 encontra a correspondência exata (em qualquer ordem). -1 encontra o menor valor maior ou igual a lookup_value (precisa estar em ordem decrescente).

A função “ MATCH ” não diferencia maiúsculas de minúsculas, então trata letras maiúsculas e minúsculas da mesma forma. Se você estiver trabalhando com texto e quiser usar caracteres curinga como * (qualquer número de caracteres) ou ? (um único caractere), certifique-se de definir match_type como 0. Porque esse é o único modo que aceita a busca com curinga.

Como INDEX() e MATCH() funcionam juntos no Excel?

Uma das formas mais comuns de usar o MATCH() é junto com a função INDEX(). Enquanto MATCH() encontra a posição de um valor, INDEX() pega o valor nessa posição. 

Aninhar MATCH() dentro de INDEX() te dá uma fórmula que é dinâmica e resiliente quando as colunas ou linhas mudam com o tempo. 

Por exemplo, eu tenho um conjunto de dados de amostra. E se eu quiser descobrir o que está na mesma linha em coluna B que Banana na coluna A, posso usar INDEX MATCH juntos assim:

=INDEX(B2:B5, MATCH("Banana", A2:A5, 0))

Aqui, “ MATCH("Banana", A2:A5, 0) ” encontra a posição de “Banana” na coluna A e “ INDEX(B2:B5, ...) ” mostra o valor da mesma linha na coluna B.

INDEX MATCH vs. VLOOKUP(): Qual você deve usar?

INDEX MATCH pode fazer o que o VLOOKUP() não consegue. Mas, escolher entre eles depende do nível de flexibilidade e desempenho que você precisa nas suas planilhas do Excel. Vamos comparar.

Flexibilidade das colunas e integridade dos dados

Uma das maiores desvantagens do VLOOKUP() é que ele dependeem números fixos de colunas. Se você inserir ou mover colunas, sua fórmula pode quebrar.

Olha só esse exemplo:

Se uma nova coluna for adicionada entre Coluna A e Coluna B, a terceira coluna se desloca e a fórmula retorna resultados incorretos, como você pode ver na imagem abaixo.

=VLOOKUP("Apple", A2:C5, 2, FALSE)

INDEX MATCH evita esse problema trabalhando com intervalos definidos em vez de posições de colunas codificadas:

=INDEX(B2:B5, MATCH("Apple", A2:A5, 0))

Mesmo que as colunas sejam reorganizadas, essa fórmula continua funcionando, o que a torna mais confiável e fácil de manter. Aqui, uma coluna é inserida antes da coluna B; a fórmula é atualizada automaticamente e os resultados continuam corretos. Essa é a magia de um INDEX MATCH.

Direção de pesquisa e versatilidade

VLOOKUP() Só dá pra procurar da esquerda pra direita. Isso quer dizer que a coluna que você está procurando precisa estar sempre na primeira posição do seu intervalo. Se o valor que você precisa estiver à esquerda da coluna de pesquisa, o VLOOKUP() não vai conseguir achar.

Mas, se a gente procurar o nome da fruta pra achar a cor dela à direita, essa fórmula vai funcionar. Por exemplo, se a gente quiser achar a cor de uma banana na nossa lista, ele mostra o resultado certo: 

Com o INDEX MATCH, essa limitação não existe. Você pode procurar em qualquer direção: para a esquerda, direita, cima ou baixo. Ele também dá suporte a pesquisas horizontais pra você ter mais controle sobre como organizar seus dados. 

Vamos aplicar o método de pesquisa de posição ( INDEX MATCH ) a este exemplo e ver como ele permite pesquisas à esquerda e à direita.

Pra pesquisa à esquerda, dá pra usar a fórmula abaixo, que vai te dar o resultado certo: 

=INDEX(A2:A5, MATCH(“yellow”, B2:B5,0))

Para a pesquisa certa, dá pra usar a fórmula abaixo, que vai te dar o resultado certo: 

=INDEX(B2:B5, MATCH(“Banana”,A2:A5,0))

Desempenho e eficiência no Excel

VLOOKUP() procura em toda a tabela pra achar uma correspondência e pega um valor de uma vez só. Mas INDEX MATCH divide a tarefa: MATCH() encontra a linha ou coluna, e INDEX() pega o valor. 

Isso torna o programa mais rápido e eficiente em termos de memória, especialmente em pastas de trabalho complexas ou com grande volume.

Aqui está uma tabela para você ter uma ideia melhor de qual função suporta qual funcionalidade:

Quando usar INDEX MATCH

Você pode usar o INDEX MATCH quando:

• Você está trabalhando com grandes conjuntos de dados, onde a velocidade é importante
• Seu modelo é dinâmico, com colunas sendo adicionadas ou reorganizadas.
• Você precisa procurar os valores à esquerda ou em intervalos horizontais.

Se seus arquivos Excel são pequenos e simples, o VLOOKUP() ainda pode dar conta do recado. Mas, pra modelagem e relatórios mais avançados, o INDEX MATCH é uma aposta mais segura.

Como usar o INDEX MATCH passo a passo

Digamos que você tem duas colunas: Frutas e vendas. Você quer saber o valor das vendas da “Apple”.

Para isso, digite a fórmula combinada INDEX MATCH e aperte Enter:

=INDEX(B2:B6, MATCH("Apple", A2:A6, 0))

Aqui, MATCH("Apple", A2:A6, 0) encontra a posição de “Apple” na lista de frutas . E INDEX(B2:B6, ...) usa essa posição pra devolver o valor de vendas correspondente da colunaVendas do .

Dá pra ver que a fórmula mostrou 12.000, que é o valor das vendas da Apple. Então, esse é o seu resultado.

Como evitar erros de INDEX MATCH

Embora “ INDEX MATCH ” seja uma boa combinação, ainda pode dar erros se não estiver configurada corretamente. Veja como resolver os problemas mais comuns e evitar erros que podem estragar suas fórmulas.

Erros #N/A e como resolver

O erro “ #N/A ” quer dizer que o Excel não conseguiu achar o valor que você está procurando. Algumas causas comuns por trás disso podem ser:

• Erros de digitação, espaços extras ou texto que não bate (por exemplo, “apple” em vez de “apples”) "Apple")

• Sem valor na faixa de pesquisa

• Errado match_type, por exemplo, 1 em vez de 0 para uma correspondência exata.

• Caracteres ocultos (como espaços não separáveis) nos dados

Então, tem duas maneiras de resolver isso:

Use TRIM() para limpar espaços à esquerda ou à direita:

=MATCH(TRIM("Apple"), A2:A6, 0)

Adicione IFERROR() para mostrar um valor alternativo em vez de um erro:

=IFERROR(INDEX(B2:B6, MATCH("Apple", A2:A6, 0)), "Not found")

Erros #REF! nas fórmulas INDEX MATCH

O erro “ #REF! ” geralmente indica uma referência quebrada, muitas vezes devido a:

• Tem um problema com o número de linhas nos intervalos INDEX() e MATCH().

• Linhas ou colunas excluídas usadas na sua fórmula

• Copiar e colar fórmulas sem atualizar as referências das células

Mas tem umas maneiras fáceis de resolver isso: 

• Vê se as faixas de tamanho do arquivo “ INDEX() ” e “ MATCH() ” são iguais:

=INDEX(B2:B6, MATCH("Apple", A2:A6, 0))

• Bloqueie intervalos com referências absolutas (como $A$2:$A$6) para manter as fórmulas estáveis durante o copiar e colar.

• Não apague células que as fórmulas dependem.

Desempenho lento e conjuntos de dados grandes

Se você estiver trabalhando com milhares de linhas, fórmulas não otimizadas do tipo “ INDEX MATCH ” podem deixar o Excel lento.

Veja como você pode otimizar isso:

• Limite os intervalos de pesquisa em vez de referenciar colunas inteiras:

=INDEX(B2:B1000, MATCH("Apple", A2:A1000, 0))

• Evite funções voláteis como OFFSET(), INDIRECT()e NOW() perto das suas pesquisas.

• Use tabelas do Excel ou intervalos nomeados para manter tudo organizado e melhorar o desempenho.

Como lidar com erros de transbordamento no Excel 365

O Excel 365 traz as matrizes dinâmicas, que podem “espalhar” os resultados nas células ao redor. Se tiver algum problema com esse processo, você vai ver um erro erro #SPILL!.

Algumas causas comuns podem ser:

• As células adjacentes não estão vazias.
• A fórmula mostra vários valores em um intervalo de células bloqueadas ou mescladas.
• Você tá usando uma matriz dinâmica onde só precisa de um único valor.

Mas esses problemas podem ser resolvidos. Veja como você pode superar esses desafios:

• Limpe as células abaixo ou ao lado da fórmula.
• Desjunte todas as células na área de transbordamento.
• Se você só quer um valor, force um único resultado com @:

=@INDEX(B2:B6, MATCH("Apple", A2:A6, 0))

Considerações finais

Se você tem usado o VLOOKUP(),, tá na hora de mudar pra algo maisestável. Você pode começar com algumas pesquisas simples usando INDEX MATCH em uma cópia do seu arquivo de trabalho. Tenta trocar a nova fórmula onde as mudanças nas colunas causam erros ou onde a estrutura dos teus dados precisa de flexibilidade. Depois que você ver como ele lida com esses casos de forma tão clara, vai ser difícil voltar atrás.