ÍNDICE & CORRESP – Excel e Google Planilhas

Este tutorial vai ensinar como usar a combinação ÍNDICE & CORRESP para realizar pesquisas no Excel e no Google Planilhas.

ÍNDICE e CORRESP, o Par Perfeito

Vamos dar uma olhada mais de perto em algumas das maneiras de combinar as funções ÍNDICE e CORRESP. A função CORRESP foi projetada para retornar a posição relativa de um item em uma matriz, enquanto a função ÍNDICE pode buscar um item de uma matriz dada uma posição específica. Essa sinergia entre as duas permite que elas executem praticamente qualquer tipo de pesquisa de que você possa precisar.

A combinação ÍNDICE / CORRESP tem sido usada historicamente como substituta da função PROCV. Um dos principais motivos é a capacidade de fazer uma pesquisa à esquerda (consulte a próxima seção).

Observação: a nova função PROCX pode realizar pesquisas voltadas para a esquerda.

Pesquisa à Esquerda

Vamos usar essa tabela de estatísticas de basquete:

Queremos encontrar o número do jogador de Bob. Como o número do jogador está à esquerda da coluna de nome, não podemos usar um PROCV.

Em vez disso, poderíamos fazer uma solicitação CORRESP básica para calcular a linha de Bob

=CORRESP(H2;B2:B5;0)

Isso procurará uma correspondência exata da palavra “Bob” e, portanto, nossa função retornaria o número 2, já que “Bob” está na segunda posição.

Em seguida, podemos usar a função ÍNDICE para retornar o número do jogador, correspondente a uma linha. Por enquanto, vamos apenas inserir manualmente “2” na função:

=ÍNDICE(A2:A5; 2)

Aqui, ÍNDICE fará referência a A3, já que essa é a segunda célula dentro do intervalo A2:A5, e retornará o resultado 42. Para nosso objetivo geral, podemos combinar esses dois resultados em:

=ÍNDICE(A2:A5;CORRESP(H2;B2:B5;0))

A vantagem aqui é que conseguimos retornar um resultado de uma coluna à esquerda de onde estávamos pesquisando.

Pesquisa em Duas Dimensões

Vamos dar uma olhada na tabela anterior:

Desta vez, porém, queremos buscar uma estatística específica. Informamos que queremos pesquisar Rebotes na célula H1. Em vez de ter de escrever várias instruções SE para determinar de qual coluna obter o resultado, você pode usar uma função CORRESP novamente. A função ÍNDICE permite que você especifique o valor da linha e o valor da coluna. Vamos adicionar outra função CORRESP aqui para determinar qual coluna queremos. Isso terá a seguinte aparência

=CORRESP(H1;A1:E1;0)

Nossa célula em H1 é um menu suspenso que nos permite escolher a categoria que queremos pesquisar e, em seguida, nossa função CORRESP determina a qual coluna da tabela ela pertence. Vamos inserir essa nova parte em nossa fórmula anterior. Observe que precisamos ajustar o primeiro argumento para ser de duas dimensões, pois não queremos mais apenas um resultado da coluna A.

=ÍNDICE(A2:E5;CORRESP(H2;B2:B5;0);CORRESP(H1;A1:E1;0))

Em nosso exemplo, queremos encontrar rebotes para Charlie. Nossa fórmula avaliará isso da seguinte forma:

=ÍNDICE(A2:E5;CORRESP("Charlie";B2:B5;0);CORRESP("Rebotes";A1:E1;0))
=ÍNDICE(A2:E5; 3; 4)
=D4

=6

Agora criamos uma configuração flexível que permite ao usuário obter qualquer valor que desejar da nossa tabela sem precisar escrever várias fórmulas ou ramificar instruções SE.

Várias Seções

A função ÍNDICE tem um quinto argumento, não muito utilizado, que pode ser fornecido para determinar qual área dentro do primeiro argumento deve ser usada. Isso significa que precisamos de uma maneira para passar várias áreas para o primeiro argumento. Você pode fazer isso usando um conjunto extra de parênteses. Este exemplo ilustra como você pode buscar resultados de diferentes tabelas em uma planilha usando ÍNDICE .

Este é o layout que usaremos. Temos estatísticas de três quartos diferentes de um jogo.

Nas células H1:H3, criamos listas suspensas de Validação de dados para nossas várias opções. A lista suspensa para o quarto vem de J2:J4. Usaremos isso para outra instrução CORRESP, para determinar qual área usar. Nossa fórmula em H4 terá a seguinte aparência:

=ÍNDICE((A3:E6; A10:E13; A17:E20);CORRESP(H2;B3:B6;0);CORRESP(H1;A2:E2;0);CORRESP(H3;J2:J4;0))

Já discutimos como as duas funções CORRESP internas funcionam, portanto, vamos nos concentrar no primeiro e no último argumentos:

=ÍNDICE((A3:E6; A10:E13; A17:E20); ...; CORRESP(H3; J2:J4; 0))

Fornecemos à função ÍNDICE vários vetores no primeiro argumento, colocando-os entre parênteses. Outra maneira de fazer isso é usar Fórmulas – Definir nome. Você poderia definir um nome chamado “MyTables” com uma definição de:

='Multiple Sections'!$A$3:$E$6;'Multiple Sections'!$A$10:$E$13;'Multiple Sections'!$A$17:$E$20

Dessa forma, estamos obtendo a seguinte fórmula:

=ÍNDICE(MyTable;CORRESP(H2;Table134[Nome];0);CORRESP(H1;Table134[#Cabeçalhos];0);CORRESP(H3;J2:J4;0))

Vamos voltar à declaração completa. Nossas várias funções CORRESP dirão à função ÍNDICE exatamente onde procurar. Primeiro, determinaremos que “Charlie” está na terceira linha. Em seguida, queremos “Rebotes”, que é a quarta coluna. Por fim, determinamos que queremos o resultado da segunda tabela. A fórmula será avaliada dessa forma:

=ÍNDICE((A3:E6; A10:E13; A17:E20); CORRESP(H2; B3:B6; 0); CORRESP(H1; A2:E2; 0); CORRESP(H3; J2:J4; 0))
=ÍNDICE((A3:E6; A10:E13; A17:E20); 3; 4; 2)
=ÍNDICE(A10:E13; 3; 4)
=D13

=14

Como mencionamos no início deste exemplo, você está limitado a ter as tabelas na mesma planilha. Se você puder escrever maneiras corretas de informar ao seu ÍNDICE  de qual linha, coluna e/ou área deseja recuperar os dados, o ÍNDICE o atenderá muito bem.

Planilhas Google -ÍNDICE & CORRESP

Todos os exemplos acima funcionam exatamente da mesma forma no Google Planilhas e no Excel.