ÍNDICE e CORRESP do Excel: Pesquisas básicas e avançadas

No Excel, recuperar dados específicos com precisão é uma necessidade frequente. Embora as funções ÍNDICE e CORRESP tenham seus próprios pontos fortes, combiná-las desbloqueia um conjunto poderoso de ferramentas para pesquisa de dados. Juntas, elas permitem uma variedade de possibilidades de pesquisa, desde buscas horizontais e verticais básicas até funcionalidades avançadas, como pesquisas bidirecionais, diferenciação entre maiúsculas e minúsculas e buscas com múltiplos critérios. Oferecendo recursos superiores ao PROCV, a combinação de ÍNDICE e CORRESP proporciona opções de pesquisa de dados muito mais amplas. Neste tutorial, vamos explorar a fundo as possibilidades que essas funções podem alcançar juntas.

• Função ÍNDICE
• Função CORRESP
• Combinar ÍNDICE e CORRESP

• Pesquisa bidirecional
• Pesquisa à esquerda
• Pesquisa sensível a maiúsculas e minúsculas
• Correspondência mais próxima
• Pesquisa com múltiplos critérios
• Pesquisa em várias colunas
• Pesquisa pelo primeiro valor não vazio
• Pesquisa pelo primeiro valor numérico
• Pesquisa por associações de valor máximo ou mínimo
• Dica: Personalize suas próprias mensagens de erro #N/D

Como usar ÍNDICE e CORRESP no Excel

Antes de utilizarmos as funções ÍNDICE e CORRESP, vamos entender primeiro como elas podem nos ajudar a pesquisar valores.

Como usar a função ÍNDICE no Excel

A função ÍNDICE no Excel retorna o valor em uma determinada localização dentro de um intervalo específico. A sintaxe da função ÍNDICE é a seguinte:

• matriz (obrigatório) refere-se ao intervalo de onde você deseja retornar o valor.
• núm_linha (obrigatório, a menos que núm_coluna esteja presente) refere-se ao número da linha da matriz.
• núm_coluna (opcional, mas obrigatório se núm_linha for omitido) refere-se ao número da coluna da matriz.

Por exemplo, para saber a nota de Jeff, o6º aluno da lista, você pode usar a função ÍNDICE assim:

=INDEX(C2:C11,6)

√ Observação: O intervalo C2:C11 é onde as notas estão listadas, enquanto o número6 encontra a nota do6º aluno.

Vamos fazer um pequeno teste. Para a fórmula =ÍNDICE(A1:C1;2), qual valor será retornado? --- Sim, será retornado Data de nascimento, o 2º valor na linha fornecida.

Agora já sabemos que a função ÍNDICE pode funcionar perfeitamente com intervalos horizontais ou verticais. Mas e se precisarmos que ela retorne um valor em um intervalo maior, com várias linhas e colunas? Nesse caso, devemos aplicar tanto o número da linha quanto o número da coluna. Por exemplo, para encontrar a nota de Jeff dentro de uma tabela, em vez de apenas uma coluna, podemos localizar sua nota usando o número da linha6 e o número da coluna3 nas células de A2 até C11, assim:

=INDEX(A2:C11,6,3)

No entanto, para um banco de dados realmente grande com várias linhas e colunas, não é prático aplicar a fórmula informando exatamente o número da linha e da coluna. É aí que devemos combinar o uso da função CORRESP.

Como usar a função CORRESP no Excel

A função CORRESP no Excel retorna um valor numérico, indicando a posição de um item específico dentro do intervalo fornecido. A sintaxe da função CORRESP é a seguinte:

• valor_procurado (obrigatório) refere-se ao valor a ser localizado na matriz_procurada.
• matriz_procurada (obrigatório) refere-se ao intervalo de células onde você deseja que o CORRESP pesquise.
• tipo_correspondência (opcional): 1, 0 ou -1.
  • 1(padrão), CORRESP encontrará o maior valor que seja menor ou igual ao valor_procurado. Os valores na matriz_procurada devem estar em ordem crescente.
  • 0, CORRESP encontrará o primeiro valor que seja exatamente igual ao valor_procurado. Os valores na matriz_procurada podem estar em qualquer ordem. (Para os casos em que o tipo de correspondência é definido como0, você pode usar caracteres curinga.)
  • -1, CORRESP encontrará o menor valor que seja maior ou igual ao valor_procurado. Os valores na matriz_procurada devem estar em ordem decrescente.

Por exemplo, para saber a posição de Vera na lista de nomes, você pode usar a fórmula CORRESP assim:

=MATCH("Vera",A2:A11,0)

√ Observação: O resultado “4” indica que o nome “Vera” está na4ª posição da lista.

Agora que conhecemos os usos básicos das funções ÍNDICE e CORRESP no Excel, vamos arregaçar as mangas e aprender a combiná-las.

Como combinar ÍNDICE e CORRESP no Excel

Veja o exemplo abaixo para entender como podemos combinar as funções ÍNDICE e CORRESP:

Para encontrar a nota de Evelyn, sabendo que as notas estão na3ª coluna, podemos usar a função CORRESP para determinar automaticamente a posição da linha, sem precisar contar manualmente. Em seguida, utilizamos a função ÍNDICE para retornar o valor na interseção da linha identificada e da3ª coluna:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

Como a fórmula pode parecer um pouco complexa, vamos analisar cada parte dela.

A fórmula ÍNDICE contém três argumentos:

• núm_linha: CORRESP("Evelyn";A2:A11;0)fornece à ÍNDICE a posição da linha do valor "Evelyn" no intervalo A2:A11, que é5.
• núm_coluna:3 especifica a3ª coluna para que a ÍNDICE localize a nota dentro da matriz.
• matriz: A2:C11 instrui a ÍNDICE a retornar o valor correspondente na interseção da linha e coluna especificadas, dentro do intervalo de A2 até C11. Por fim, obtemos o resultado90.

Na fórmula acima, utilizamos um valor fixo, "Evelyn". No entanto, na prática, valores fixos não são práticos, pois exigiriam alteração toda vez que quisermos pesquisar outros dados, como a nota de outro aluno. Nesses casos, podemos usar referências de célula para criar fórmulas dinâmicas. Por exemplo, aqui vou trocar "Evelyn" por F2:

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

Exemplos de fórmulas com ÍNDICE e CORRESP

Nesta seção, vamos abordar diferentes situações para usar as funções ÍNDICE e CORRESP conforme as necessidades.

ÍNDICE e CORRESP para realizar uma pesquisa bidirecional

No exemplo anterior, sabíamos o número da coluna e usamos uma fórmula CORRESP para encontrar o número da linha. Mas e se não soubermos também o número da coluna?

Nesses casos, podemos realizar uma pesquisa bidirecional, também conhecida como pesquisa matricial, usando duas funções CORRESP: uma para encontrar o número da linha e outra para determinar o número da coluna. Por exemplo, para saber a nota de Evelyn, devemos usar a seguinte fórmula:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

ÍNDICE e CORRESP para realizar uma pesquisa à esquerda

Agora, considere um cenário em que você precisa descobrir a turma de Evelyn. Você pode notar que a coluna da turma está à esquerda da coluna de nomes, uma situação que vai além das capacidades de outra poderosa função de pesquisa do Excel, o PROCV.

Na verdade, a capacidade de realizar pesquisas à esquerda é um dos pontos em que a combinação de ÍNDICE e CORRESP supera o PROCV.

Para encontrar a turma de Evelyn, utilize a seguinte fórmula para pesquisar por Evelyn em B2:B11 e retornar o valor correspondente de A2:A11.

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

ÍNDICE e CORRESP para realizar uma pesquisa sensível a maiúsculas e minúsculas

A função CORRESP, por padrão, não diferencia maiúsculas de minúsculas. No entanto, quando você precisa que sua fórmula distinga entre letras maiúsculas e minúsculas, é possível aprimorá-la incorporando a função EXATO. Ao combinar a função CORRESP com EXATO em uma fórmula ÍNDICE, você pode realizar uma pesquisa sensível a maiúsculas e minúsculas, conforme mostrado abaixo:

• matriz refere-se ao intervalo de onde você deseja retornar o valor.
• valor_procurado refere-se ao valor a ser localizado, considerando a diferenciação entre maiúsculas e minúsculas, na matriz_procurada.
• matriz_procurada refere-se ao intervalo de células onde você deseja que o CORRESP compare com o valor_procurado.

Por exemplo, para saber a nota de JIMMY, utilize a seguinte fórmula:

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

√ Observação: Esta é uma fórmula matricial que exige que você pressione Ctrl + Shift + Enter, exceto no Excel365, Excel2021 e versões mais recentes.

ÍNDICE e CORRESP para encontrar a correspondência mais próxima

No Excel, você pode se deparar com situações em que precisa encontrar o valor mais próximo de um valor específico dentro de um conjunto de dados. Nesses casos, a combinação das funções ÍNDICE e CORRESP, juntamente com ABS e MÍNIMO, pode ser extremamente útil.

• matriz refere-se ao intervalo de onde você deseja retornar o valor.
• matriz_procurada refere-se ao intervalo de valores onde você deseja encontrar a correspondência mais próxima do valor_procurado.
• valor_procurado refere-se ao valor para o qual se deseja encontrar a correspondência mais próxima.

Por exemplo, para descobrir de quem é a nota mais próxima de85, utilize a seguinte fórmula para pesquisar a nota mais próxima de85 em C2:C11 e retornar o valor correspondente de A2:A11.

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

√ Observação: Esta é uma fórmula matricial que exige que você pressione Ctrl + Shift + Enter, exceto no Excel365, Excel2021 e versões mais recentes.

ÍNDICE e CORRESP para realizar pesquisa com múltiplos critérios

Para encontrar um valor que atenda a múltiplas condições, exigindo pesquisa em duas ou mais colunas, utilize a fórmula abaixo. Ela permite realizar uma pesquisa de várias condições especificando diferentes critérios em colunas distintas, ajudando você a encontrar o valor desejado que atenda a todos os critérios.

√ Observação: Esta é uma fórmula matricial que exige que você pressione Ctrl + Shift + Enter. Um par de chaves aparecerá na barra de fórmulas.

• matriz refere-se ao intervalo de onde você deseja retornar o valor.
• (valor_procurado=matriz_procurada) representa uma condição. Esta condição verifica se um determinado valor_procurado corresponde aos valores na matriz_procurada.

Por exemplo, para encontrar a nota da Coco da Turma A, cuja data de nascimento é02/07/2008, você pode usar a seguinte fórmula:

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0))

ÍNDICE e CORRESP para realizar pesquisa em várias colunas

Imagine um cenário em que você trabalha com várias colunas de dados. A primeira coluna serve como chave para classificar os dados das demais colunas. Para determinar a categoria ou classificação de um item específico, será necessário pesquisar entre as colunas de dados e associá-lo à chave correspondente na coluna de referência.

Por exemplo, na tabela abaixo, como podemos associar o aluno Shawn à sua respectiva turma usando ÍNDICE e CORRESP? É possível com uma fórmula, mas ela é bastante extensa e difícil de compreender, além de ser complicada para memorizar e digitar.

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

É aí que o recurso Índice e correspondência de várias colunas do Kutools para Excel se destaca. Ele simplifica o processo, tornando rápido e fácil associar itens específicos às suas categorias correspondentes. Para acessar essa poderosa ferramenta e associar Shawn à sua turma com facilidade, basta baixar e instalar o complemento Kutools para Excel e seguir estes passos:

1. Selecione a célula de destino onde deseja exibir a turma correspondente.
2. Na guia Kutools, clique em Assistente de Fórmulas > Procurar & Referência > Índice e correspondência de várias colunas.
3. 
3. Na caixa de diálogo que será exibida, siga estes passos:
   1. Clique no1º botão ao lado de Coluna de pesquisa para selecionar a coluna que contém a informação-chave que você deseja retornar, ou seja, os nomes das turmas. (Aqui, só é possível selecionar uma coluna.)
   2. Clique no2º botão ao lado de Intervalo da tabela para selecionar as células que serão usadas para corresponder aos valores na coluna de pesquisa selecionada Coluna de pesquisa, ou seja, os nomes dos alunos.
   3. Clique no3º botão ao lado de Valor de pesquisa para selecionar a célula que contém o nome do aluno que você deseja associar à turma, neste caso, Shawn.
   4. Clique em OK.
   5. 

Resultado

O Kutools gerou automaticamente a fórmula, e você verá o nome da turma de Shawn exibido imediatamente na célula de destino.

ÍNDICE e CORRESP para pesquisar o primeiro valor não vazio

Para recuperar o primeiro valor não vazio, ignorando erros, de uma coluna ou linha, você pode usar uma fórmula baseada nas funções ÍNDICE e CORRESP. No entanto, se não quiser ignorar os erros do seu intervalo, adicione a função ÉCÉL.VAZIA.

• Obter o primeiro valor não vazio em uma coluna ou linha ignorando erros:

• =ÍNDICE(B4:B15;CORRESP(VERDADEIRO;ÍNDICE((B4:B15<>0);0);0))

• Obter o primeiro valor não vazio em uma coluna ou linha incluindo erros:

• =ÍNDICE(B4:B15;CORRESP(FALSO;ÉCÉL.VAZIA(B4:B15);0))

  

ÍNDICE e CORRESP para pesquisar o primeiro valor numérico

Para recuperar o primeiro valor numérico de uma coluna ou linha, utilize a fórmula baseada nas funções ÍNDICE, CORRESP e ÉNÚM.

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

ÍNDICE e CORRESP para pesquisar associações de valor máximo ou mínimo

Se você precisa recuperar um valor associado ao valor máximo ou mínimo dentro de um intervalo, pode usar as funções MÁXIMO ou MÍNIMO juntamente com ÍNDICE e CORRESP.

• ÍNDICE e CORRESP para recuperar um valor associado ao valor máximo:
• =ÍNDICE(matriz, CORRESP(MÁXIMO(matriz_procurada), matriz_procurada,0))
• ÍNDICE e CORRESP para recuperar um valor associado ao valor mínimo:
• =ÍNDICE(matriz, CORRESP(MÍNIMO(matriz_procurada), matriz_procurada,0))
• Há dois argumentos nas fórmulas acima:
• • matriz refere-se ao intervalo de onde você deseja retornar as informações relacionadas.
  • matriz_procurada representa o conjunto de valores a serem examinados ou pesquisados por critérios específicos, ou seja, valores máximos ou mínimos.

Por exemplo, se você quiser saber quem tem a maior nota, utilize a seguinte fórmula:

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

Dica: Personalize suas próprias mensagens de erro #N/D

Ao trabalhar com as funções ÍNDICE e CORRESP do Excel, você pode se deparar com o erro #N/D quando não houver resultado correspondente. Por exemplo, na tabela abaixo, ao tentar encontrar a nota de uma aluna chamada Samantha, aparece o erro #N/D, pois ela não está presente no conjunto de dados.

Para tornar suas planilhas mais amigáveis, você pode personalizar essa mensagem de erro envolvendo sua fórmula ÍNDICE CORRESP na função SEERRO.ND:

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

Acima estão todos os conteúdos relevantes sobre as funções ÍNDICE e CORRESP no Excel. Espero que o tutorial seja útil. Se você deseja explorar mais dicas e truques do Excel, clique aqui para acessar nossa coleção com milhares de tutoriais.