Funções ÍNDICE e CORRESP no Excel – Substitua o PROCV

Este tutorial mostra como usar as funções ÍNDICE e CORRESP no Excel e como elas podem ser tão úteis quanto o PROCV.

Em alguns artigos recentes, expliquei o básico da função PROCV além de fornecer alguns exemplos de fórmulas, para usuários mais avançados. Agora, tentarei convence-lo a não usar o PROCV. Farei isso mostrando uma maneira alternativa de se fazer uma pesquisa no Excel.

Entretanto, não duvido que alguém me pergunte: “Mas para que eu preciso disso?”. Bom, como o PROCV não é a única função de pesquisa no Excel, e suas inúmeras limitações podem impedir que se obtenha resultados desejado em diversas situações, por outro lado, a função ÍNDICE CORRESP é mais flexível e possue vários recursos que a torna superior ao PROCV em muitos aspectos.

Funções ÍNDICE CORRESP do Excel – o básico

Como o objetivo deste tutorial é demonstrar uma maneira alternativa de realizar uma consulta no Excel, usando uma combinação das funções ÍNDICE e CORRESP, não vou focar muito na sintaxe e no uso delas. Abordaremos apenas o mínimo necessário para entender a ideia geral e, em seguida, examinaremos detalhadamente os exemplos de fórmulas que revelam todas as vantagens de usar ÍNDICE CORRESP em vez de PROCV.

Função ÍNDICE – sintaxe e uso

A função ÍNDICE do Excel retorna um valor em uma matriz com base nos números de linhas e colunas especificados. A sintaxe da função ÍNDICE é a seguinte:

= ÍNDICE(matriz;núm_linha;[núm_coluna])

Aqui está uma explicação simples de cada parâmetro da função:

• matriz – um intervalo de células das quais deseja retornar um valor.
• núm_linha – o número da linha na matriz da qual deseja retornar um valor. Se omitido, o argumento núm_coluna é obrigatório.
• núm_coluna – o número da coluna na matriz da qual deseja retornar um valor. Se omitido, núm_linha é necessário.

Para mais informações, consulte Função ÍNDICE do Excel.

Temos aqui um exemplo simples da fórmula ÍNDICE:

= ÍNDICE (A1:C10;2;3)

A fórmula acima pesquisa o valor correspondente a segunda linha e a terceira coluna, no intervalo das células A1 a C10, ou seja, retorna o valor contido na célula C2.

Fácil né? Mas, quando trabalhamos com dados reais fica difícil saber em quais linhas e colunas encontra-se os dados , então é aí que a função CORRESP é útil.

Função CORRESP – sintaxe e uso

A função CORRESP do Excel procura um valor de pesquisa em um intervalo de células e retorna a posição relativa desse valor no intervalo.

A sintaxe da função CORRESP é a seguinte:

= CORRESP (valor_procurado; matriz_procurada; [tipo_correspondência])

• valor_procurado – o número ou valor do texto que deseja procurar.
• matriz_procurada – intervalo de células para pesquisada.
• tipo_correspondência – especifica se a correspondência deve ser exata ou aproximada. Por exemplo:

1 ou omitido – localiza o maior valor que é menor ou igual ao valor_procurado. Requer a classificação da matriz de pesquisa em ordem crescente.
0 – localiza o primeiro valor exatamente igual ao valor da pesquisa. Na combinação ÍNDICE CORRESP, quase sempre é necessário uma correspondência exata; portanto, defina o terceiro argumento da sua função CORRESP para 0.
-1 – localiza o menor valor que é maior ou igual ao valor_procurado. Requer a classificação da matriz de pesquisa em ordem decrescente.

Se no intervalo B1: B3 contiver os valores “Nova York”, “Paris”, “Londres”, a fórmula abaixo retornará o número 3. Isso porque a palavra “Londres” é a terceira linha no intervalo especificado:

= CORRESP ("Londres"; B1:B3;0)

Para mais informações, consulte Função CORRESP do Excel.

A princípio, a utilidade da função CORRESP pode parecer questionável. Porque, quem se importa com a posição de um valor em um intervalo? O que queremos saber na maioria das vezes é o próprio valor e não a sua posição, certo?

Bom, deixe-me lembrá-lo de que a posição relativa do valor da pesquisa (ou seja, números de linhas e colunas) é exatamente o que precisamos fornecer para os argumentos núm_linha e núm_coluna da função ÍNDICE.

Como usar a função ÍNDICE CORRESP no Excel

Agora que recapitulamos o básico, acredito que já começou a fazer sentido como as funções CORRESP e ÍNDICE trabalham juntas. Em poucas palavras, a função ÍNDICE encontra o valor da pesquisa por números de coluna e linha, e a função CORRESP fornece esses números. É isso aí!

Para fazer uma pesquisa no Excel, a função CORRESP é usada apenas para determinar o número da linha e fornecer o intervalo da coluna diretamente para a função ÍNDICE:

= ÍNDICE (coluna indicada para retornar um valor; CORRESP (valor de pesquisa; coluna procurada; 0))

Como um exemplo fica mais fácil de entender!

Suponha que tenhamos uma lista com as capitais nacionais e sua população:

Nota importante! O número de linhas no argumento da matriz ÍNDICE deve corresponder ao número de linhas no argumento matriz_procurada da função CORRESP, caso contrário, a fórmula produzirá um resultado incorreto.

Mas calma! … por que simplesmente não usamos a seguinte fórmula do PROCV? Qual é o sentido de perder tempo tentando descobrir as reviravoltas misteriosas das funções CORRESP ÍNDICE do Excel?

= PROCV (F1; A2:C10;3; FALSO)

Nesse caso, não faz sentido 🙂 Este exemplo simples é apenas para fins de demonstração, para que se tenha uma ideia de como as funções ÍNDICE e CORRESP funcionam juntas. Outros exemplos a seguir mostram o poder real dessa combinação que lida facilmente com muitos cenários complexos quando o PROCV falha.

ÍNDICE CORRESP vs. PROCV

Ao decidir qual função usar nas pesquisas, a maioria dos gurus do Excel concorda que o ÍNDICE CORRESP é muito melhor que o PROCV. No entanto, muitas pessoas ainda permanecem no PROCV, em primeiro lugar, porque é mais simples e, em segundo lugar, porque não compreendem completamente todos os benefícios do uso da fórmula ÍNDICE CORRESP no Excel. Sem essa compreensão, ninguém está disposto a investir seu tempo para aprender uma sintaxe mais complexa.

Abaixo, mostrarei as principais vantagens do ÍNDICE CORRESP sobre o PROCV e você decide qual delas prefere!

4 razões principais para usar ÍNDICE CORRESP em vez de PROCV

1. Pesquisa da direita para a esquerda. Como qualquer usuário instruído sabe, o PROCV não pesquisa dados a esquerda, o que significa que o valor da pesquisa deve sempre residir nas primeiras colunas da tabela (ou seja, mais à esquerda da tabela). Já a função ÍNDICE CORRESP pode fazer pesquisas à esquerda com facilidade! O exemplo a seguir mostra isso em ação: Como exibir um valor à esquerda no Excel.

2. Insere ou exclui colunas com segurança. As fórmulas do PROCV quebram resultados ou fornecem eles de forma incorreta quando uma nova coluna é excluída ou adicionada a uma tabela de pesquisa. Isso porque, a sintaxe do PROCV exige a especificação do número de índice da coluna que se deseja extrair os dados. Naturalmente, quando se adiciona ou exclui colunas, o número do índice é alterado.

Com ÍNDICE CORRESP, é possível especificar o intervalo da coluna de retorno, não um número de índice. Como resultado, permite inserir e remover quantas colunas quiser, sem se preocupar em atualizar todas as fórmulas associadas.

3. Não há limite para o tamanho de um valor de pesquisa. Ao usar a função PROCV, o comprimento total dos seus critérios de pesquisa não pode exceder 255 caracteres; caso contrário, retorna o erro # VALOR!. Portanto, se o conjunto de dados contiver cadeias longas, ÍNDICE CORRESP é uma solução funcional.

4. Maior velocidade de processamento. Se as tabelas forem relativamente pequenas, dificilmente haverá diferença significativa no desempenho do Excel. Mas se as planilhas contiverem centenas ou milhares de linhas e, consequentemente, centenas ou milhares de fórmulas, o ÍNDICE CORRESP funcionará muito mais rápido que o PROCV, pois o Excel precisará processar apenas as colunas de pesquisa e retorno, em vez de toda a matriz da tabela.

O impacto do PROCV no desempenho do Excel pode ser especialmente perceptível se sua pasta de trabalho contiver fórmulas de matriz complexas como PROCV e SOMA. O ponto é que a verificação de cada valor na matriz requer uma chamada separada da função PROCV. Portanto, quanto mais valores tiver a matriz e mais fórmulas de matriz você tiver em uma pasta de trabalho, mais lento será o Excel

Função ÍNDICE CORRESP – exemplos de fórmulas

Conhecendo os motivos para aprender a função ÍNDICE CORRESP, vamos à parte mais interessante e ver como podemos aplicar o conhecimento teórico na prática.

Fórmula ÍNDICE CORRESP para procurar da direita para a esquerda

Como já mencionado, o PROCV não pesquisa dados a esquerda da matriz. A menos que, os valores de pesquisa estejam apresentados na coluna mais à esquerda. Caso contrário, não há chance de que uma fórmula PROCV traga o resultado desejado. A função ÍNDICE CORRESP é mais inteligente nesse sentido, pois independente da localização das colunas de pesquisa.

Neste exemplo, adicionaremos a coluna “Classificação” à esquerda da nossa tabela de amostra e tentaremos descobrir como a capital russa, Moscou, é classificada em termos de população.

Com o valor de pesquisa na célula G1, use a fórmula abaixo para procurar o dado no intervalo C2: C10 e retornar um valor correspondente a classificação (apresentados nos intervalos A2: A10):

= ÍNDICE (A2:A10;CORRESP(G1;C2:C10;0))

Dica. Se planeja usar a fórmula ÍNDICE CORRESP para mais de uma célula, bloqueie os dois intervalos com referências absolutas de célula (como $A$2:$A$10 e $C$2:4C$10) para que não fiquem distorcidos quando copiando a fórmula.

ÍNDICE CORRESP para pesquisar em linhas e colunas

Nos exemplos acima, usamos ÍNDICE CORRESP como substituto do PROCV, clássico para retornar um valor de um intervalo predefinido de uma coluna. Mas, e se for necessário procurar o dado em várias linhas e colunas? Em outras palavras, e se quisermos executar a chamada matriz ou pesquisa bidirecional?

Isso pode parecer complicado, mas a fórmula é muito semelhante à função básica ÍNDICE CORRESP do Excel, com apenas uma diferença:

Use duas funções CORRESP – uma para obter um número de linha e a outra para obter um número de coluna.

= ÍNDICE (matriz, CORRESP (valor de pesquisa; coluna a ser pesquisada; 0); CORRESP (valor de pesquisa; linha a ser pesquisada; 0))

Agora, dê uma olhada na tabela abaixo e vamos criar uma fórmula ÍNDICE CORRESP CORRESP para encontrar a população (em milhões) em um determinado país para um determinado ano.

Com o país de destino apresentado na célula G1 (valor de pesquisa) e o ano de destino na célula G2 (valor de pesquisa), a fórmula assume esta forma:

= ÍNDICE (B2:D10; CORRESP(G1;A2:A10;0); CORRESP (G2;B1:D1;0))

Como esta fórmula funciona:

Sempre que precisar entender uma fórmula do Excel, divida-a em partes menores e veja o que cada função individual faz.

CORRESP(G1;A2:A10;0) – pesquisa em A2:A11 o valor contido na célula G1 (“China”) e retorna sua posição, que é 2.

CORRESP(G2;B1:D1;0)) – pesquisa em B1:D1 para obter a posição do valor na célula G2 (“2015”), que é 3.

Os números de linha e coluna acima vão para os argumentos correspondentes da função ÍNDICE: ÍNDICE (B2: D10;2;3)

Como resultado, obtém um valor na interseção da 2ª linha e da 3ª coluna no intervalo B2: D10, que é o valor na célula D3. Fácil? Sim!

Função ÍNDICE CORRESP para procurar vários critérios

Se já teve a chance de ler o tutorial do Função PROCV, provavelmente já testou uma fórmula no PROCV com vários critérios. No entanto, uma limitação significativa dessa abordagem é a necessidade de adicionar uma coluna auxiliar. A boa notícia é que a função ÍNDICE CORRESP do Excel também pode procurar com dois ou mais critérios, sem modificar ou reestruturar os dados de origem!

Aqui está a fórmula genérica de ÍNDICE CORRESP com vários critérios:

{= ÍNDICE (intervalo_de_ retorno, CORRESP (1; (critério1 = intervalo1) *) (critério2 = intervalo2); 0)}}

Dica. Essa é uma fórmula de matriz que deve ser concluída com o atalho Ctrl + Shift + Enter.

A seguinte fórmula ÍNDICE CORRESP funciona da seguinte maneira:

= ÍNDICE (C2:C10;CORRESP (1;(F1=A2:A10) * (F2=B2:B10);0))

• C2:C10 é o intervalo para o qual se deseja retornar um valor;
• F1 é o critério 1;
• A2:A10 é o intervalo a ser comparado com os critérios 1;
• F2 é o critério 2;
• B2:B10 é o intervalo a ser comparado com os critérios 2.

Lembre-se de inserir a fórmula corretamente, pressionando Ctrl + Shift + Enter, e o Excel incluirá automaticamente os colchetes, como mostrado na figura abaixo:

Se preferir não usar fórmulas de matriz nas planilhas, adicione mais uma função ÍNDICE à fórmula e complete clicando na tecla Enter

Como essas fórmulas funcionam:

As fórmulas usam a mesma abordagem que a função ÍNDICE CORRESP básica. Para avaliar vários critérios, crie duas ou mais matrizes de valores VERDADEIRO e FALSO e multiplique os elementos correspondentes dessas matrizes. A operação de multiplicação converte VERDADEIRO e FALSO em 1 e 0, e produz uma matriz em que 1 corresponde a linhas que correspondem a todos os critérios. A função CORRESP com o valor de pesquisa 1 encontra o primeiro “1” na matriz e passa sua posição para ÍNDICE, que retorna um valor nessa linha da coluna especificada.

Essa é uma explicação avançada da fórmula. Para detalhes completos, consulte ÍNDICE CORRESP com vários critérios.

ÍNDICE CORRESP do Excel com MÉDIA, MÁXIMO, MÍNIMO.

O Microsoft Excel possui funções especiais para encontrar um valor mínimo, máximo e médio em um intervalo. Mas, e se for preciso obter um valor de outra célula associada a esses valores? Nesse caso, use a função MÁXIMO, MÍNIMO ou MÉDIA junto com ÍNDICE CORRESP.

ÍNDICE CORRESP com MÁXIMO

Para encontrar o maior valor na coluna D e retornar um valor da coluna C na mesma linha, use a fórmula:

= ÍNDICE(C2 C10;CORRESP(MÁXIMO(D2:D10);D2:D10;0))

ÍNDICE CORRESP com MÍNIMO

Para localizar o menor valor na coluna D e extrair um valor associado da coluna C, use esta:

= ÍNDICE(C2:C10;CORRESP(MÍNIMO(D2:D10);D2:D10;0))

ÍNDICE CORRESP com MÉDIA

Para calcular o valor mais próximo da média nos D2:D10 e, obter o valor correspondente da coluna C, a fórmula abaixo deve ser usada:

= ÍNDICE(C2:C10;CORRESP(MÉDIA(D2:D10);D2:D10;-1))

Dependendo de como os dados estão organizados, forneça 1 ou -1 ao terceiro argumento (tipo_correspondência) da função CORRESP:

• Se a coluna de pesquisa (coluna D no caso) estiver classificada em ordem crescente, coloque 1. A fórmula calculará o maior valor que é menor ou igual ao valor médio.
• Se a coluna de pesquisa estiver classificada como decrescente, digite -1. A fórmula calculará o menor valor que é maior ou igual ao valor médio.
• Se a matriz de pesquisa contiver um valor exatamente igual à média, podemos inserir 0 para a correspondência exata. Nenhuma classificação é necessária.

No exemplo, as populações na coluna D são classificadas em ordem decrescente, portanto usamos -1 para o tipo de correspondência. Como resultado, obtemos a palavra “Tóquio”, pois sua população (13.189.000) é a correspondência mais próxima, maior que a média (12.269.006).

Usando ÍNDICE CORRESP com SENÃODISP / SEERRO

Se uma fórmula ÍNDICE CORRESP no Excel não conseguir encontrar um valor de pesquisa, ela produzirá um erro # N/D. Se desejado substituir a notação de erro padrão por algo mais significativo, envolva a fórmula ÍNDICE CORRESP na função SENÃODISP. Por exemplo:

= SENÃODISP (ÍNDICE(C2:C10; CORRESP(F1;A2:A10;0));"Nenhuma correspondência encontrada")

Agora, se alguém inserir uma tabela que não existe no intervalo de pesquisa, a fórmula informará claramente ao usuário que nenhuma correspondência foi encontrada:

Se deseja capturar todos os erros, não apenas # N/A, use a função SEERRO em vez de SENÃODISP:

= SEERRO (ÍNDICE (C2:C10; CORRESP (F1;A2:A10;0));"Ops, ocorreu um erro!")

Lembre-se de que, em muitas situações, é imprudente disfarçar todos os erros, pois eles alertam sobre possíveis falhas na fórmula.

Bom, chegamos ao fim! é assim que se usa ÍNDICE CORRESP no Excel. Espero que estes exemplos de fórmulas sejam úteis para você e espero vê-lo no blog na próxima semana!

Conclusão

Neste artigo vimos como as funções ÍNDICE e CORRESP juntas, apresentam lógicas mais avançada comparado ao PROCV, e como elas podem ser úteis quando trabalhada com outras funções, como MÁXIMO, MÍNIMO, MÉDIA, SENÃODISP E SEERRO.

Recomendo fortemente que você tome coragem de repetir todo o passo-a-passo. Eu penso que praticar é a melhor maneira de aprender e, embora pareça simples, é preciso se acostumar a navegar entre as telas e fórmulas do Excel para que no futuro você possa se tornar um usuário avançado.

O site deste pessoal aqui foi referência para este artigo. O site é muitoooo bacana com várias dicas legais para Excel, mas infelizmente o conteúdo está em inglês, o que não deixa de ser uma boa referência para quem conseguir acompanhar. Eu recomendo.

Portanto, continue lendo mais sobre Excel avançado aqui.