No Excel, a recuperação precisa de dados específicos costuma ser uma necessidade frequente. Embora as funções INDEX e MATCH tenham seus próprios pontos fortes, combiná-las desbloqueia um poderoso conjunto de ferramentas para pesquisa de dados. Juntos, eles facilitam uma variedade de recursos de pesquisa, desde pesquisas horizontais e verticais básicas até funcionalidades mais avançadas, como pesquisas bidirecionais, com distinção entre maiúsculas e minúsculas e multicritérios. Oferecendo recursos aprimorados em comparação com VLOOKUP, o emparelhamento de INDEX e MATCH permite uma gama mais ampla de opções de pesquisa de dados. Neste tutorial, vamos nos aprofundar nas possibilidades que eles podem alcançar juntos.

• Função ÍNDICE
• Função MATCH
• Combine INDEX e MATCH

• Pesquisa bidirecional
• Pesquisa à esquerda
• Pesquisa que diferencia maiúsculas de minúsculas
• Partida mais próxima
• Pesquisa com vários critérios
• Pesquisa em várias colunas
• Pesquisa pelo primeiro valor não em branco
• Pesquisa pelo primeiro valor numérico
• Pesquisa de associações MAX ou MIN
• Dica: personalize suas próprias mensagens de erro #N/A

Como usar INDEX e MATCH no Excel

Antes de usarmos as funções INDEX e MATCH, vamos ter certeza de que sabemos como INDEX e MATCH podem nos ajudar a procurar valores primeiro.

Como usar a função INDEX no Excel

A ÍNDICE função no Excel retorna o valor em um determinado local em um intervalo específico. A sintaxe da função INDEX é a seguinte:

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

Por exemplo, para saber a pontuação de Jeff, 6º aluno da lista, você pode usar a função INDEX assim:

=INDEX(C2:C11,6)

√ Nota: o intervalo C2: C11 é onde estão as pontuações listadas, enquanto o número 6 encontra a pontuação do exame do 6o aluno.

Aqui vamos fazer um pequeno teste. Para a fórmula =ÍNDICE(A1:C1,2), qual valor ele retornará? --- Sim, ele retornará Data de nascimento, 2º valor na linha fornecida.

Agora devemos saber que a função INDEX pode funcionar perfeitamente com faixas horizontais ou verticais. Mas e se precisarmos que ele retorne um valor em um intervalo maior com várias linhas e colunas? Bem, neste caso, devemos aplicar um número de linha e um número de coluna. Por exemplo, para descobrir Pontuação de Jeff dentro do intervalo da tabela em vez de em uma única coluna, podemos localizar sua pontuação com um número da linha 6 e de um número da coluna de 3 no células através de A2 a C11 como isso:

=INDEX(A2:C11,6,3)

No entanto, para um banco de dados realmente grande com várias linhas e colunas, certamente não é conveniente para nós aplicarmos a fórmula com um número de linha e coluna exatos. E é isso quando devemos combinar o uso da função MATCH.

Incrível! Usando guias eficientes no Excel como Chrome, Edge, Firefox e Safari!
Economize 50% do seu tempo e reduza milhares de cliques do mouse para você todos os dias!

Como usar a função MATCH no Excel

A função MATCH no Excel retorna um valor numérico, a localização de um item específico no intervalo determinado. A sintaxe da função MATCH é a seguinte:

• lookup_value (obrigatório) refere-se ao valor a ser correspondido no lookup_array.
• lookup_array (obrigatório) refere-se ao intervalo de células onde você deseja que o MATCH pesquise.
• tipo de partida (opcional): 1, 0 or -1.
  • 1 (padrão), MATCH encontrará o maior valor que é menor ou igual ao lookup_value. Os valores no lookup_array deve ser colocado em ordem crescente.
  • 0, MATCH encontrará o primeiro valor que é exatamente igual ao lookup_value. Os valores no lookup_array pode estar em qualquer ordem. (Para os casos em que o tipo de correspondência é definido como 0, você pode usar caracteres curinga.)
  • -1, MATCH encontrará o menor valor maior ou igual ao lookup_value. Os valores no lookup_array deve ser colocado em ordem decrescente.

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

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

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

Agora que sabemos sobre os usos básicos das funções INDEX e MATCH no Excel, vamos arregaçar as mangas e nos preparar para combinar as duas funções.

Como combinar INDEX e MATCH no Excel

Por favor, veja o exemplo abaixo para descobrir como podemos combinar as funções INDEX e MATCH:

Para encontrar Pontuação de Evelyn, com o conhecimento de que as notas do exame estão no 3terceira coluna, podemos use a função MATCH para determinar automaticamente a posição da linha sem a necessidade de contá-lo manualmente. Posteriormente, podemos empregar a função INDEX para recuperar o valor na intersecção da linha identificada e a 3ª coluna:

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

Bem, como a fórmula pode parecer um pouco complicada, vamos examinar cada parte dela.

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

• row_num: CORRESP("Evelyn",A2:A11,0) fornece INDEX com a posição da linha do valor "Evelyn"na faixa A2: A11, o que é 5.
• núm_coluna: 3 especifica o 3Terceira coluna para INDEX para localizar a pontuação dentro da matriz.
• ordem: A2: C11 instrui INDEX a retornar o valor correspondente na interseção da linha e coluna especificadas, dentro do intervalo que vai de A2 a C11. Finalmente, obtemos o resultado 90.

Na fórmula acima, empregamos um valor codificado, "Evelyn". Porém, na prática, valores codificados são impraticáveis, pois exigiriam modificações cada vez que buscássemos dados diferentes, como a pontuação de outro aluno. Nesses cenários, podemos utilizar referências de células para criar fórmulas dinâmicas. Por exemplo, neste caso, vou mude "Evelyn" para F2:

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

Exemplos de fórmulas INDEX e MATCH

Nesta parte, falaremos sobre diferentes circunstâncias para usar as funções INDEX e MATCH para atender a diferentes necessidades.

INDEX e MATCH para aplicar uma pesquisa bidirecional

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

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

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

INDEX e MATCH para aplicar uma pesquisa à esquerda

Agora, vamos considerar um cenário em que você precisa determinar a classe de Evelyn. Você deve ter notado que a coluna da classe está posicionada à esquerda da coluna do nome, uma situação que excede os recursos de outra poderosa função de pesquisa do Excel, VLOOKUP.

Na verdade, a capacidade de realizar pesquisas no lado esquerdo é um dos aspectos em que a combinação de INDEX e MATCH supera VLOOKUP.

Para encontrar Aula de Evelyn, use a seguinte fórmula para procure por Evelyn em B2:B11 e recupere o valor correspondente de A2:A11.

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

INDEX e MATCH para aplicar uma pesquisa que diferencia maiúsculas de minúsculas

As funções MATCH são inerentemente insensíveis a maiúsculas e minúsculas. No entanto, quando você precisar que sua fórmula diferencie caracteres maiúsculos e minúsculos, você pode aprimorá-la incorporando o EXATO função. Ao combinar a função MATCH com EXACT em uma fórmula INDEX, você pode realizar efetivamente uma pesquisa com distinção entre maiúsculas e minúsculas, conforme mostrado abaixo:

• ordem refere-se ao intervalo de onde você deseja retornar o valor.
• lookup_value refere-se ao valor a ser correspondido, considerando o caso dos caracteres, no lookup_array.
• lookup_array refere-se ao intervalo de células onde você deseja que MATCH compare com lookup_value.

Por exemplo, para saber Pontuação do exame do JIMMY, use a seguinte fórmula:

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

√ Nota: Esta é uma fórmula de matriz que requer que você insira com Ctrl + Shift + Entrar, exceto no Excel 365 e Excel 2021.

INDEX e MATCH para encontrar uma correspondência mais próxima

No Excel, você pode encontrar situações em que precisa encontrar a correspondência mais próxima ou mais próxima de um valor específico em um conjunto de dados. Nesses cenários, usar uma combinação das funções INDEX e MATCH, junto com as funções ABS e MIN, pode ser extremamente útil.

• ordem refere-se ao intervalo de onde você deseja retornar o valor.
• lookup_array refere-se ao intervalo de valores onde você deseja encontrar a correspondência mais próxima lookup_value.
• lookup_value refere-se ao valor para encontrar sua correspondência mais próxima.

Por exemplo, para descobrir cuja pontuação está mais próxima de 85, use a seguinte fórmula para procure a pontuação mais próxima de 85 em C2:C11 e recupere o valor correspondente de 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 você insira com Ctrl + Shift + Entrar, exceto no Excel 365 e Excel 2021.

Incrível! Usando guias eficientes no Excel como Chrome, Edge, Firefox e Safari!
Economize 50% do seu tempo e reduza milhares de cliques do mouse para você todos os dias!

INDEX e MATCH para aplicar uma pesquisa com vários critérios

Para encontrar um valor que atenda a diversas condições, exigindo a pesquisa em duas ou mais colunas, use a fórmula a seguir. A fórmula permite realizar uma pesquisa multicritério especificando várias condições em diferentes colunas, ajudando a encontrar o valor desejado que atenda a todos os critérios especificados.

√ Nota: Esta é uma fórmula de matriz que requer que você insira com Ctrl + Shift + Entrar. Um par de chaves aparecerá na barra de fórmulas.

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

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

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

INDEX e MATCH para aplicar uma pesquisa em várias colunas

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

Por exemplo, na tabela abaixo, como podemos combinar o aluno Shawn com sua turma correspondente usando INDEX e MATCH? Bem, você pode conseguir isso com uma fórmula, mas a fórmula é bastante extensa e pode ser difícil de compreender, muito menos de lembrar 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)), "")

Isso e onde Kutools for Excel's Indexar e combinar em múltiplas colunas recurso é útil. Ele simplifica o processo, tornando rápido e fácil combinar entradas específicas com suas categorias correspondentes. Para desbloquear esta ferramenta poderosa e combinar facilmente Shawn com sua classe, basta baixe e instale o complemento Kutools para Excele faça o seguinte:

1. Selecione a célula de destino onde deseja exibir a classe correspondente.
2. No Kutools guia, clique em Fórmula Helper > Pesquisa e Referência > Indexar e combinar em múltiplas colunas.
3. 
3. Na caixa de diálogo pop-up, faça o seguinte:
   1. Clique no 1º botão ao lado de coluna_pesquisa para selecionar a coluna que contém as principais informações que você deseja retornar, ou seja, os nomes das classes. (Você só pode selecionar uma única coluna aqui.)
   2. Clique no 2º botão ao lado de Tabela_rng para selecionar as células para corresponder aos valores no selecionado coluna_pesquisa, ou seja, os nomes dos alunos.
   3. Clique no 3º botão ao lado de Valor_procurado para selecionar a célula que contém o nome do aluno que deseja associar à turma, neste caso, Shawn.
   4. Clique OK.
   5. 

Resultado

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

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

Para recuperar o primeiro valor não em branco, ignorando erros, de uma coluna ou linha, você pode usar uma fórmula baseada nas funções INDEX e MATCH. Porém, se você não quiser ignorar os erros do seu intervalo, adicione a função ISBLANK.

• Obtenha o primeiro valor não em branco em uma coluna ou linha ignorando erros:

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

• Obtenha o primeiro valor não em branco em uma coluna ou linha, incluindo erros:

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

  

INDEX e MATCH para procurar o primeiro valor numérico

Para recuperar o primeiro valor numérico de uma coluna ou linha, use a fórmula baseada nas funções INDEX, MATCH e ISNUMBER.

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

INDEX e MATCH para procurar associações MAX ou MIN

Se precisar recuperar um valor associado ao valor máximo ou mínimo dentro de um intervalo, você pode usar a função MAX ou MIN junto com as funções INDEX e MATCH.

• INDEX e MATCH para recuperar um valor associado ao valor máximo:
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
• INDEX e MATCH 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:
• • ordem refere-se ao intervalo de onde você deseja retornar as informações relacionadas.
  • lookup_array 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 determinar quem tem a pontuação mais alta, utilize a seguinte fórmula:

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

Dica: personalize suas próprias mensagens de erro #N/A

Ao trabalhar com as funções INDEX e MATCH do Excel, você pode encontrar o erro #N/A quando não há resultado correspondente. Por exemplo, na tabela abaixo, ao tentar encontrar a pontuação de uma aluna chamada Samantha, aparece um erro #N/A porque ela não está presente no conjunto de dados.

Para tornar suas planilhas mais fáceis de usar, você pode personalizar esta mensagem de erro agrupando sua fórmula INDEX MATCH na função IFNA:

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

Acima está todo o conteúdo relevante relacionado às funções INDEX e MATCH no Excel. Espero que você ache o tutorial útil. Se você deseja explorar mais dicas e truques do Excel, clique aqui para acessar nossa extensa coleção de mais de milhares de tutoriais.