ÍNDICE e CORRESP no Excel: A Alternativa para o PROCV

agosto 3, 2019novembro 30, 2021 admin 0 comentários

Quer aprender a usar as funções ÍNDICE e CORRESP no Excel? Então você está no lugar certo!

Se você já sabe como usar a função PROCV no Excel, então deve saber que ela possui algumas limitações. E na maioria dos casos, uma simples combinação ÍNDICE e CORRESP é capaz de lhe dar mais flexibilidade.

Recentemente escrevemos um artigo onde mostramos a diferença entre usar a função PROCV ou usar as funções ÍNDICE e CORRESP. (Você pode conferir aqui este artigo).

E se o seu objetivo é dominar o uso dessas duas funções juntas, então com certeza você vai gostar deste tutorial onde separamos exemplos práticos de uso das funções ÍNDICE e CORRESP. Confira!

Função ÍNDICE e CORRESP no Excel

Como o objetivo deste tutorial é demonstrar uma maneira alternativa à função PROCV no Excel usando a combinação das funções ÍNDICE e CORRESP, não iremos nos aprofundar muito em suas sintaxes e usos individuais. Nós apenas mostraremos o mínimo necessário para que você entenda a ideia geral e, em seguida, analisaremos de maneira mais detalhada os exemplos.

Função ÍNDICE

A função Excel ÍNDICE retorna um valor em uma matriz com base nos números de linha e coluna especificados. Sua sintaxe é:

ÍNDICE(matriz;núm_linha;[núm_coluna])
ÍNDICE(ref;núm_linha;[núm_coluna];[núm_área])

A função ÍNDICE possui 2 sintaxes. A primeira é usada na maioria dos casos, no entanto, no caso de pesquisas de três vias, a segunda sintaxe é usada (confira o uso de ambos os casos aqui).

Argumentos de entrada

matriz – um intervalo de células ou uma constante de matriz.
núm_linha – o número da linha a partir da qual o valor deve ser buscado.
[núm_coluna] – o número da coluna a partir da qual o valor deve ser buscado. Embora este seja um argumento opcional, se o núm_linha não for fornecido, então será necessário fornecer o critério núm_coluna.
[núm_área] – (Critério Opcional) Se o argumento da matriz for composto de vários intervalos, esse número será usado para selecionar a referência de todos os intervalos.

Digamos que você tenha um intervalo de 4 células, por exemplo, A1:A4 e deseja saber o valor da célula A3. Como A3 é o terceiro valor nesse intervalo, usaremos a seguinte fórmula:

=ÍNDICE(A1:A4;3)

Muito fácil, certo? No entanto, ao trabalhar com dados reais, você dificilmente saberá qual linha e coluna deseja, e é por isso que precisará do auxílio da função CORRESP.

Função CORRESP

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

Por exemplo, se o intervalo A1:A3 tiver os valores “São Paulo”, “Rio de Janeiro”, “Minas Gerais”, a fórmula =CORRESP(“Minas Gerais”;B1:B3;0) irá retornar o número 3, porque “Minas Gerais” é a terceira entrada no intervalo.

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

CORRESP(valor_proc; matriz_proc; [tipo_corresp])

Onde:

valor_proc: (obrigatório) – o valor que você deseja encontrar. Pode ser um valor numérico, de texto ou lógico, ou uma referência de célula.
matriz_proc: (obrigatório) – o intervalo de células para pesquisar.
tipo_corresp: (opcional) – define o tipo de correspondência. Pode ser um desses valores: 1, 0, -1. O argumento tipo_corresp definido como 0 retorna apenas a correspondência exata, enquanto os outros dois tipos permitem a correspondência aproximada.

1 ou omitido (padrão) – encontre o maior valor na matriz de pesquisa que seja menor ou igual ao valor de pesquisa. Requer a classificação do array de pesquisa em ordem crescente, do menor para o maior ou de A para Z.
0 – encontre o primeiro valor na matriz que é exatamente igual ao valor de pesquisa. Nenhuma classificação é necessária.
-1 – encontre o menor valor na matriz que seja maior ou igual ao valor de pesquisa. A matriz de pesquisa deve ser classificada em ordem decrescente, da maior para a menor ou de Z para A.

Você poderá conferir mais detalhes e exemplos da função CORRESP neste artigo.

À primeira vista, a função CORRESP pode parecer inútil, afinal, quem se importa com a posição de um valor em um intervalo? Pois o que queremos realmente saber é o valor em si.

Deixe-me lembrá-lo de que a posição relativa do valor de pesquisa (isto é, uma linha ou número de coluna) é exatamente o que você precisa fornecer ao argumento núm_linha ou / e núm_coluna da função ÍNDICE. Como você viu anteriormente, a função ÍNDICE pode retornar o valor na junção de uma determinada linha e coluna, mas não pode determinar qual linha e coluna exatamente você deseja.

Como usar ÍNDICE e CORRESP no Excel

Agora que você já aprendeu o básico dessas duas funções, acredito que já começou a fazer sentido como CORRESP e ÍNDICE trabalham juntas.

A função CORRESP determina a posição relativa do valor de pesquisa no intervalo de células especificado. A partir daí, a função ÍNDICE pega esse número ou números e retorna um valor na célula correspondente.

Ainda está com dificuldades? Tente pensar em ÍNDICE e CORRESP da seguinte maneira:

ÍNDICE(coluna para retornar um valor de;CORRESP(valor de pesquisa;coluna a ser consultada;0))

Para ficar ainda mais fácil de entender vejamos um exemplo na prática. Suponha que você tenha uma lista de capitais nacionais conforma imagem abaixo:

Vamos encontrar a população de algum estado, digamos a capital da Bahia, usando a seguinte fórmula:

=ÍNDICE($D$2:$D$10;CORRESP("Bahia";$B$2:$B$10;0))

Agora, vamos analisar o que cada bloco dessa fórmula faz:

A função CORRESP procura o valor de pesquisa “Bahia” no intervalo B2:B10 e retorna o número 4, porque “Bahia” é o quarto na matriz de pesquisa.
A posição relativa do valor de pesquisa vai diretamente para o argumento núm_linha do ÍNDICE instruindo-o a retornar um valor dessa linha.
Então, a fórmula acima se transforma em um ÍNDICE simples ($D$2:$D$10;3) que diz para pesquisar nas células D2 a D10 e retornar o valor da 4ª célula nesse intervalo, ou seja, célula D5 porque começamos a contar da segunda linha.

Importante! O número de linhas no argumento da matriz de ÍNDICE deve corresponder ao número de linhas no argumento matriz_procurada da CORRESP, caso contrário, a fórmula retornará um resultado incorreto.

Se você chegou até aqui então deve estar se perguntando: Por que simplesmente não usamos a seguinte fórmula de PROCV? Qual é o sentido de perder tempo com CORRESP / ÍNDICE?

=PROCV("Bahia";$B$2:$D$10;3)

Neste caso, nenhum 🙂 O exemplo acima serve apenas para fins de demonstração, para que você tenha uma ideia de como as funções ÍNDICE e CORRESP funcionam juntas. Outros exemplos que seguem abaixo mostrarão o real poder dessas duas funções aninhadas que facilmente lida com muitos cenários complexos quando o PROCV não dá conta do recado.

Por que ÍNDICE e CORRESP é melhor que o PROCV?

Ao decidir qual fórmula usar para pesquisas verticais, a maioria dos especialistas em Excel concordam que o ÍNDICE /CORRESP é muito melhor do que o PROCV. No entanto, muitos usuários do Excel ainda recorrem ao PROCV porque é uma função mais simples. Isso acontece porque poucas pessoas entendem completamente todos os benefícios de mudar do PROCV para o ÍNDICE e CORRESP e, sem essa compreensão, ninguém está disposto a investir seu tempo para aprender uma fórmula mais complexa.

Abaixo, apresentamos as principais vantagens de usar ÍNDICE e CORRESP no Excel e, em seguida, você decide se prefere ficar com PROCV ou alternar para ÍNDICE e CORRESP.

4 principais vantagens do uso do ÍNDICE e CORRESP no Excel

1. Consulta da direita para a esquerda: Como qualquer usuário instruído sabe, a função PROCV não pode procurar valores à esquerda de uma matriz, o que significa que seu valor de pesquisa deve estar sempre na coluna mais à esquerda do intervalo de pesquisa. Por outro lado ÍNDICE e CORRESP pode fazer uma pesquisa à esquerda com facilidade! O exemplo a seguir demonstra esse recurso em ação – Como visualizar um valor à esquerda no Excel.

2. Inserir ou excluir colunas com segurança: As fórmulas PROCV são quebradas ou retornam resultados incorretos quando uma nova coluna é excluída ou adicionada a uma tabela de consulta. Com o PROCV qualquer coluna inserida ou excluída altera os resultados retornados por suas fórmulas porque a sintaxe da função PROCV requer a especificação de toda a matriz da tabela e um determinado número indicando de qual coluna você deseja extrair os dados. Por exemplo, se você tiver uma tabela A1:D10 e quiser retornar um valor da coluna B, você colocará “2” no terceiro parâmetro (núm_índice_coluna) de uma fórmula PROCV, digamos =PROCV(“valor procurado”; A1:D10;2).

Se posteriormente você inserir uma nova coluna entre A e B, você terá que alterar “2” para “3” em sua fórmula, caso contrário, retornará um valor da coluna recém-inserida.

Com o ÍNDICE e CORRESP, você pode excluir ou inserir novas colunas em sua tabela de consulta sem distorcer os resultados, pois você especifica diretamente a coluna que contém o valor que deseja obter. E isso é realmente um grande benefício, especialmente ao trabalhar com grandes conjuntos de dados, já que você é capaz de inserir e remover colunas sem se preocupar em atualizar todas as fórmulas associadas de PROCV.

3. Nenhum limite para o tamanho de um valor de pesquisa: Ao usar a função PROCV, lembre-se de que o comprimento total de seus critérios de pesquisa não deve exceder 255 caracteres, caso contrário, você terá o erro #VALOR!. Portanto, se seu conjunto de dados contiver muitos critérios de pesquisa, ÍNDICE e CORRESP será a única solução. Suponha que você esteja usando a seguinte fórmula PROCV que pesquisa nas células B5 a D10 pelo valor na célula A2:

=PROCV(A2;B5:D10;3;FALSO)

A fórmula não funcionará se o valor de pesquisa na célula A2 exceder 255 caracteres. Em vez disso, você deve usar a função ÍNDICE e CORRESP da seguinte maneira:

=ÍNDICE(D5:D10;CORRESP(VERDADEIRO;ÍNDICE(B5:B10=A2;0);0))

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

ÍNDICE e CORRESP: Exemplos práticos

Agora que você já conhece as razões para aprender e usar a função ÍNDICE e CORRESP, vamos para a parte mais interessante e confira como você pode aplicar o conhecimento teórico na prática.

Exemplo 1: Como procurar da direita para a esquerda com ÍNDICE e CORRESP

Conforme dito anteriormente, a função PROCV não permite realizar buscas à esquerda de uma matriz. Portanto, a menos que sua coluna de pesquisa seja a coluna mais à esquerda no intervalo de pesquisa, não há chance de que uma fórmula de PROCV retorne o resultado desejado.

Por outro lado, função ÍNDICE e CORRESP é mais flexível e realmente não se importa onde está a coluna de retorno. Como exemplo, usaremos novamente a tabela com as capitais nacionais por população. Desta vez, vamos criar uma fórmula ÍNDICE e CORRESP que descubra qual é a classificação da capital da Bahia em termos população.

=ÍNDICE($A$2:$A$10;CORRESP("Bahia";$B$2:$B$10;0))

Como funciona a fórmula?

Primeiro, você escreve uma fórmula CORRESP simples que encontra a posição da Bahia: =CORRESP(“Bahia”;$B$2:$B$10;0))
Em seguida, você determina o parâmetro da matriz para sua função ÍNDICE, que é a coluna A em nosso caso (A2:A10).
Por fim, basta montar as duas partes juntas para obter a seguinte fórmula: =ÍNDICE($A$2:$A$10;CORRESP(“Bahia”;$B$2:$B$10;0))

Dica Extra: É sempre bom usar referências absolutas de célula nas fórmulas ÍNDICE e CORRESP para que os intervalos de pesquisa não fiquem distorcidos quando você copiar a fórmula para outras células.

Você também pode substituir o valor de pesquisa por uma referência de célula:

=ÍNDICE($A$2:$A$10;CORRESP(F3;$B$2:$B$10;0))

Exemplo 2: ÍNDICE e CORRESP com MÁXIMO MÍNIMO e MÉDIA

Você pode aninhar outras funções do Excel dentro da fórmula ÍNDICE e CORRESP, por exemplo, para encontrar o valor mínimo ou máximo, ou o valor mais próximo da média no intervalo. Abaixo estão alguns exemplos de fórmulas para o exemplo anterior:

Pontos a serem lembrados ao usar MÉDIA com ÍNDICE e CORRESP

Ao usar a função MÉDIA combinada com ÍNDICE e CORRESP, na maioria das vezes você digitará “1” ou “-1” no terceiro argumento (tipo_correspondência) da função CORRESP, a menos que tenha certeza de que sua matriz de consulta contém um valor exatamente igual para a média. Se este for o caso, você pode inserir “0” para correspondência exata.

Se você definir 1, os valores na coluna de pesquisa deverão ser classificados em ordem crescente e a fórmula retornará o maior valor que seja menor ou igual ao valor médio.

Se você definir -1, os valores na coluna de pesquisa deverão ser classificados em ordem decrescente e o menor valor maior ou igual ao valor médio será retornado.

Em nosso exemplo, os valores na coluna D são classificados em ordem crescente, então usamos “1” como o tipo de correspondência, e nossa fórmula MÉDIA + ÍNDICE e CORRESP retorna “Brasília”, já que sua população (2.974.703) é a mais próxima “menor que”corresponde ao número médio (3.933.723).

Exemplo 3: Usando ÍNDICE e CORRESP para pesquisar por valores de linha e coluna

Neste exemplo usaremos a função ÍNDICE e CORRESP de maneira muito semelhante a outras fórmulas que já discutimos neste tutorial, com a única diferença. Conforme vimos anteriormente, a sintaxe da função ÍNDICE permite retornar um valor em uma matriz com base nos números de linha e coluna

E para começar, vamos criar a fórmula geral para realizar uma pesquisa de matriz. Para isso vamos pegar a fórmula ÍNDICE e CORRESP que você já conhece e adicionar mais uma função CORRESP, que retornará o número da coluna:

=ÍNDICE(tabela de consulta;CORRESP(valor de pesquisa vertical;coluna a ser consultada;0);CORRESP(valor de pesquisa horizontal;linha a ser consultada;0))

Importante: É necessário especificar a tabela inteira no argumento da matriz da função ÍNDICE em caso de consulta bidirecional.

E agora, vamos aplicar esse modelo de fórmula na prática. Abaixo, você vê uma lista dos países mais populosos do mundo. Suponha que você queira saber a população da China no ano 2000:

Uma dica criar uma fórmula complexa no Excel com uma ou várias funções aninhadas, é sempre escrever cada função individual primeiro. Então, neste exemplo você pode começar escrevendo duas funções CORRESP que retornarão os números de linha e coluna para sua função ÍNDICE.

CORRESP Vertical – Fará a busca na coluna B, mais precisamente no intervalo B2:B11, para o valor na célula H2 ( “China”), e a função CORRESP correspondente é esta: =CORRESP($H$2;$B$1:$B$11;0). Esta fórmula retornará 3 porque “China” é o 3º item na coluna B (incluindo o cabeçalho da coluna).
CORRESP horizontal – Procura o valor na célula H3 (“2000”) na linha 1, ou seja, nas células A1 a E1: =CORRESP($H$3;$A$1:$E$1;0). Esta fórmula CORRESP retorna “4” porque “2010” é a quarta coluna.
Agora, basta colocar as fórmulas acima dentro da função ÍNDICE e para que a mágica ocorra:

=ÍNDICE($A$1:$E$11;CORRESP($H$2;$B$1:$B$11;0);CORRESP($H$3;$A$1:$E$1;0))

Se você simplesmente substituir as funções CORRESP pelos números retornados, a fórmula será muito mais fácil de ser compreendida: =ÍNDICE($A$1:$E$11;3;4)

Ou seja, o Excel retorna um valor no cruzamento da linha 3 com a coluna 4 no intervalo A1:E11, que é o valor em células D2.

Exemplo 4: ÍNDICE e CORRESP com vários critérios

Em um artigo anterior, mostramos um exemplo de como fazer um PROCV com dois ou mais critérios. No entanto, existia uma limitação na função PROCV que exigia a necessidade de uma coluna auxiliar. Mas a boa notícia é que com as funções ÍNDICE e CORRESP, você também poderá realizar buscas com dois ou mais critérios, mas sem a necessidade de colunas auxiliares.

Suponha que você tenha uma lista de pedidos e deseje encontrar a soma com base em dois critérios, ” Nome do cliente ” e ” Produto “. Um fator complicador é que um cliente pode comprar vários produtos e os nomes de clientes são listados em uma ordem aleatória na tabela de consulta:

Para isso, podemos usar a seguinte fórmula em F5:

{=ÍNDICE(C2:C10;CORRESP(1;(A2:A10=F3)*(B2:B10=F4);0))}

Esta fórmula é um pouco mais complexa do que as apresentadas anteriormente, mas com os conhecimentos adquiridos nos exemplos anteriores é possível compreendê-la facilmente.

Primeiro observe a seguinte função CORRESP: =CORRESP(1;(A2:A10=F3)*(B2:B10=F4);0)

O que temos aqui são os seguintes 3 argumentos:

Valor_procurado: 1
Matriz_procurada: (A2:A10=F3)*(B2:B10=F4)
Tipo_correspondência: 0

O primeiro e o terceiro parâmetro são claros, ou seja, a função procura “1”, e retorna o primeiro valor encontrado.

Agora, a questão principal é: por que procuramos “1”? Para saber a resposta, vamos dar uma olhada mais de perto na nossa matriz de pesquisa.

O que fazemos aqui é pegar o valor da célula F3 na coluna e verificar em todos os nomes de produtos na coluna de produtos (A2:A10). Se a correspondência for encontrada, a fórmula retornará VERDADEIRO, caso contrário, FALSO. Então fazemos o mesmo para valores nas colunas B (“Vendedores”) e comparamos com a célula F4.

Veja a imagem abaixo para melhor compreensão:

Como você provavelmente sabe, nas fórmulas do Excel, o valor lógico de VERDADEIRO é igual a 1 e FALSO é igual a 0. E como o asterisco (*) atua como o operador E em fórmulas de matriz, obtemos “1” somente se a correspondência for encontrada em ambas as colunas, caso contrário será 0.

Portanto, o resultado dessa operação é uma matriz de 1 e 0, em que “1” é o valor que atende às duas condições especificadas. Se a sua tabela de consulta não tiver linhas duplicadas, haverá apenas um “1” na matriz.

E como “1” é nosso valor de pesquisa, a função CORRESP retorna a posição relativa dessa linha.

Note que a função ÍNDICE e CORRESP não é capaz de somar os valores em caso de combinações repetidas, ou seja, se tivermos mais de uma combinação para um produto e vendedor, a função será capaz de mostrar apenas um único valor. Se você precisa existir a soma, recomenda-se usar a função SOMARPRODUTO.

Baixe aqui a planilha com os exemplos apresentados neste tutorial

Confira também as seguintes dicas de Excel:

E então, o que achou do tutorial? Deixe o seu comentário abaixo, gostaríamos muito de conhecer a sua opinião! Para conferir mais dicas de Excel, continue acompanhando o Excel Easy!