INDEX e CORRESP no Excel: procuras básicas e avançadas

No Excel, obter dados específicos com precisão é frequentemente uma necessidade recorrente. Embora as funções ÍNDICE e CORRESP tenham cada uma as suas próprias vantagens, combiná-las desbloqueia um conjunto poderoso de ferramentas para pesquisa de dados. Juntas, permitem uma variedade de capacidades de procura — desde pesquisas horizontais e verticais básicas até funcionalidades mais avançadas, como pesquisas bidirecionais, sensíveis a maiúsculas e minúsculas e com múltiplos critérios. Oferecendo capacidades superiores às do PROCV, o emparelhamento de ÍNDICE e CORRESP proporciona uma gama mais ampla de opções para localizar os dados que precisa. Neste tutorial, vamos explorar em profundidade todo o potencial que estas duas funções conseguem alcançar em conjunto.

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

• Pesquisa bidirecional
• Pesquisa à esquerda
• Pesquisa sensível a maiúsculas/minúsculas
• Correspondência mais próxima
• Pesquisa com múltiplos critérios
• Pesquisa em múltiplas colunas
• Pesquisa do primeiro valor não vazio
• Procurar o primeiro valor numérico
• Procura de associações MAX ou MIN
• Dica: Personalize as suas próprias mensagens de erro #N/D

Como utilizar ÍNDICE e CORRESP no Excel

Antes de utilizarmos as funções ÍNDICE e CORRESP, certifiquemo-nos de que compreendemos como elas nos podem ajudar a encontrar valores.

Como utilizar a função ÍNDICE no Excel

A função ÍNDICE no Excel devolve o valor numa determinada posição de um intervalo específico. A sintaxe da função ÍNDICE é a seguinte:

• matriz (obrigatório) refere-se ao intervalo de onde pretende obter o valor.
• núm_linha(obrigatório, salvo se)núm_coluna estiver 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 do Jeff, o 6º aluno da lista, pode utilizar a função ÍNDICE da seguinte forma:

=INDEX(C2:C11,6)

√ Nota: O intervalo C2:C11é onde as notas estão listadas, enquanto o número 6encontra a nota do 6º aluno.

Vamos agora fazer um pequeno teste. Para a fórmula =ÍNDICE(A1:C1;2), que valor irá devolver? — Sim, irá devolver Data de nascimento, o 2º valor na linha indicada.

Agora já sabemos que a função ÍNDICE funciona perfeitamente com intervalos horizontais ou verticais. Mas e se precisarmos que ela devolva um valor num intervalo maior, com várias linhas e colunas? Nesse caso, devemos indicar simultaneamente um número de linha e um número de coluna. Por exemplo, para descobrir a nota do Jeff dentro do intervalo da tabela — em vez de apenas numa coluna —, podemos localizar a sua nota com um número de linha 6 e um número de coluna 3 nas células de A2 a C11 da seguinte forma:

=INDEX(A2:C11,6,3)

Contudo, numa base de dados muito grande com múltiplas linhas e colunas, não será certamente prático aplicar a fórmula utilizando números exatos de linha e coluna. É precisamente nessa altura que devemos combinar a utilização da função CORRESP.

Como utilizar a função CORRESP no Excel

A função CORRESP no Excel devolve um valor numérico — a posição de um item específico dentro do intervalo indicado. A sua sintaxe é a seguinte:

• valor_procurado (obrigatório) refere-se ao valor a localizar na matriz_procura.
• matriz_procura (obrigatório) refere-se ao intervalo de células onde pretende que a função CORRESP efetue a pesquisa.
• tipo_corresp(opcional):1,0ou -1.
  • 1(predefinição): a função CORRESP localiza o maior valor que é menor ou igual ao valor_procurado. Os valores na matriz_procurada devem estar ordenados por ordem crescente.
  • 0, a função CORRESP localiza o primeiro valor exatamente igual ao valor_procurado. Os valores na matriz_procurada podem estar em qualquer ordem. (Quando o tipo de correspondência é definido como 0, pode utilizar carateres universais.)
  • -1, a função CORRESP localiza o menor valor que é maior ou igual ao valor_procurado. Os valores na matriz_procurada devem estar ordenados por ordem decrescente.

Por exemplo, para saber a posição da Vera na Lista de Nomes, pode utilizar a Distinguir Fórmulas da seguinte forma:

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

√ Nota: O resultado «4» indica que o nome «Vera» se encontra na 4.ª posição da lista.

Agora que já dominamos as utilizações básicas das funções ÍNDICE e CORRESP no Excel, vamos arregaçar as mangas e preparar-nos para combiná-las.

Como combinar ÍNDICE e CORRESP no Excel

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

Para encontrar a nota da Evelyn, sabendo que as notas dos exames estão na 3ª coluna, podemos utilizar a função CORRESP para determinar automaticamente a posição da linha, sem necessidade de contagem manual. Depois, basta usar a função ÍNDICE para obter o valor na interseção da linha identificada com a 3ª coluna:

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

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

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

• núm_linha:CORRESP(«Evelyn»;A2:A11;0)fornece ao ÍNDICE a posição da linha do valor "Evelyn" no intervalo A2:A11, que é5.
• núm_coluna: 3 especifica a 3ª coluna para que o ÍNDICE localize a pontuação dentro da matriz.
• matriz: A2:C11 indica ao ÍNDICE que devolva o valor correspondente na interseção da linha e da coluna especificadas, dentro do intervalo que vai de A2 a C11. Assim, obtemos o resultado 90.

Na fórmula acima, utilizámos um valor fixo: «Evelyn». Contudo, na prática, os valores fixos são impraticáveis, pois exigiriam modificações sempre que quiséssemos procurar dados diferentes, como a nota de outro aluno. Nestes cenários, podemos utilizar referências a células para criar fórmulas dinâmicas. Por exemplo, neste caso, irei substituir «Evelyn» por F2:

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

Exemplos de ÍNDICE e Distinguir Fórmulas

Nesta secção, exploramos diferentes cenários em que as funções ÍNDICE e CORRESP podem ser utilizadas para responder a diversas necessidades.

ÍNDICE e CORRESP para realizar uma procura bidirecional

No exemplo anterior, conhecíamos o número da coluna e utilizámos uma fórmula para identificar o número da linha. Mas e se também não soubermos o número da coluna?

Nestes casos, podemos realizar uma procura bidirecional — também conhecida como procura matricial — utilizando 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 da Evelyn, utilize a seguinte fórmula:

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

ÍNDICE e CORRESP para realizar uma procura à esquerda

Agora, considere um cenário em que precisa de determinar a turma da Evelyn. Poderá ter reparado que a coluna da turma está posicionada à esquerda da coluna dos nomes — uma situação que ultrapassa as capacidades de outra poderosa função de procura do Excel, o PROCV.

De facto, a capacidade de realizar procuras para a esquerda é um dos aspetos em que a combinação de ÍNDICE e CORRESP supera o PROCV.

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

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

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

A função CORRESP é intrinsecamente insensível a maiúsculas e minúsculas. No entanto, quando precisar que a sua fórmula distinga entre maiúsculas e minúsculas, pode aprimorá-la incorporando a função EXATO. Ao combinar a função CORRESP com a EXATO numa fórmula ÍNDICE, consegue realizar eficazmente uma pesquisa sensível a maiúsculas e minúsculas, como ilustrado abaixo:

• matriz refere-se ao intervalo de onde pretende devolver o valor.
• valor_procurado refere-se ao valor a corresponder, tendo em conta a capitalização dos caracteres, na matriz_procura.
• matriz_procura refere-se ao intervalo de células onde pretende que CORRESP compare com o valor_procurado.

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

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

√ Nota: Esta é uma fórmula de matriz que requer que seja introduzida com Ctrl+Shift+Enter, exceto no Excel 365, Excel 2021 e versões mais recentes.

ÍNDICE e CORRESP para encontrar uma correspondência aproximada

No Excel, poderá encontrar situações em que precise de encontrar a correspondência mais próxima ou aproximada a um valor específico num conjunto de dados. Nestes cenários, a combinação das funções ÍNDICE e CORRESP, juntamente com as funções ABS e MÍNIMO, pode ser extremamente útil.

• matriz refere-se ao intervalo de onde pretende devolver o valor.
• matriz_procura refere-se ao intervalo de valores onde pretende encontrar a correspondência mais próxima para o valor_procurado.
• valor_procurado refere-se ao valor cuja correspondência mais próxima pretende encontrar.

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

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

√ Nota: Esta é uma fórmula de matriz que requer que seja introduzida com Ctrl+Shift+Enter, exceto no Excel 365, Excel 2021 e versões mais recentes.

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

Para encontrar um valor que satisfaça múltiplas condições — exigindo uma pesquisa em duas ou mais colunas — utilize a seguinte fórmula. Esta permite-lhe realizar uma procura com múltiplos critérios ao especificar várias condições em colunas diferentes, ajudando-o a encontrar o valor pretendido que cumpra todos os critérios definidos.

√ Nota: Esta é uma fórmula de matriz que deve ser introduzida com Ctrl+Shift+Enter. Um par de chavetas aparecerá automaticamente na Barra de Fórmulas.

• matriz refere-se ao intervalo de onde pretende obter o valor.
• (valor_procurado=matriz_procura) representa uma condição única. Esta condição verifica se um determinado valor_procurado corresponde aos valores na matriz_procura.

Por exemplo, para encontrar a pontuação de Coco da Turma A, cuja data de nascimento é 7/2/2008, pode utilizar a seguinte fórmula:

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

INDEX e CORRESP para aplicar uma procura em múltiplas colunas

Imagine um cenário em que está a trabalhar com múltiplas colunas de dados. A primeira coluna funciona como chave para classificar os dados nas outras colunas. Para determinar a categoria ou classificação de uma entrada específica, terá de realizar uma pesquisa nas colunas de dados e associá-la à chave relevante na coluna de referência.

Por exemplo, na tabela abaixo, como podemos associar o aluno Shawn à sua turma correspondente utilizando as funções INDEX e CORRESP? Bem, pode consegui-lo com uma fórmula, mas esta é bastante extensa e pode ser difícil de compreender, quanto mais de 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)), "")

É aqui que entra em jogo a funcionalidade Kutools para Excel's Índice e correspondência de várias colunas. Esta simplifica o processo, tornando-o rápido e fácil de associar entradas específicas às respetivas categorias. Para desbloquear esta ferramenta poderosa e associar facilmente Shawn à sua turma, basta transferir e instalar o suplemento Kutools para Excel e seguir os passos abaixo:

1. Selecione a célula de destino onde pretende apresentar a turma correspondente.
2. No separador Kutools, clique em Assistente de Fórmulas > Procurar e Referência > Índice e correspondência de várias colunas.
3. 
3. Na caixa de diálogo que surge, proceda da seguinte forma:
   1. Clique no 1.º botão junto a Lookup_col para selecionar a coluna que contém a informação-chave que pretende devolver — ou seja, os nomes das turmas. (Só pode selecionar uma única coluna aqui.)
   2. Clique no 2.º botão junto a Table_rng para selecionar as células correspondentes aos valores na coluna Lookup_col selecionada — ou seja, os nomes dos alunos.
   3. Clique no 3.º botão junto a Lookup_value para selecionar a célula que contém o nome do aluno que pretende associar à respetiva turma — neste caso, Shawn.
   4. Clique em OK.
   5. 

Resultado

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

INDEX e CORRESP para procurar o primeiro valor não vazio

Para obter o primeiro valor não vazio, ignorando erros, de uma coluna ou linha, pode utilizar uma fórmula baseada nas funções INDEX e CORRESP. Contudo, se não quiser ignorar os erros no seu intervalo, adicione a função ÉCÉL.VAZIA.

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

• =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))

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

• =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

  

INDEX e CORRESP para procurar o primeiro valor numérico

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

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

INDEX e CORRESP para procurar associações com o MÁXIMO ou MÍNIMO

Se precisar de obter um valor associado ao máximo ou Valor Mínimo dentro de um intervalo, pode utilizar a função MÁXIMO ou MÍNIMO juntamente com as funções INDEX e CORRESP.

• ÍNDICE e CORRESP para recuperar um valor associado ao Valor Máximo:
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
• ÍNDICE e CORRESP para recuperar um valor associado ao Valor Mínimo:
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
• Existem dois argumentos nas fórmulas acima:
• • matriz refere-se ao intervalo de onde pretende obter a informação relacionada.
  • matriz_procura representa o conjunto de valores a examinar ou pesquisar segundo critérios específicos, ou seja, o valor máximo ou mínimo.

Por exemplo, se pretender determinar quem tem a pontuação mais elevada, utilize a seguinte fórmula:

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

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

Ao trabalhar com as funções INDEX e CORRESP do Excel, poderá encontrar o erro #N/D quando não existir um resultado correspondente. Por exemplo, na tabela abaixo, ao tentar encontrar a pontuação de uma aluna chamada Samantha, surge um erro #N/D, pois ela não está presente no conjunto de dados.

Para tornar as suas folhas de cálculo mais amigáveis, pode personalizar esta mensagem de erro envolvendo a sua função INDEX Distinguir Fórmulas na função SE.NÃO.DISP:

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

Acima encontra todo o conteúdo relevante sobre as funções INDEX e CORRESP no Excel. Esperamos que este tutorial seja útil para si! Se quiser explorar mais dicas e truques do Excel, clique aqui para aceder à nossa vasta coleção com milhares de tutoriais.