Ir para o conteúdo principal

Excel INDEX MATCH: pesquisas básicas e avançadas

Autora: Amanda Li Última modificação: 2023-12-29

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.


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

O Í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:

=INDEX(array, row_num, [column_num])
  • 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)

correspondência de índice do excel 01

√ 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)

correspondência de índice do excel 02

Coisas que devemos saber sobre a função INDEX no Excel:
  • A função INDEX pode funcionar com faixas verticais e horizontais.
  • Se ambos row_num e núm_coluna argumentos são usados, row_num vai à frente do núm_coluna, e INDEX recupera o valor na interseção do especificado row_num e núm_coluna.

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.


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:

=MATCH(lookup_value, lookup_array, [match_type])
  • 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)

correspondência de índice do excel 3

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

Coisas que devemos saber sobre a função MATCH no Excel:
  • A função MATCH retorna a posição do valor de pesquisa na matriz de pesquisa, não o valor em si.
  • A função MATCH retorna a primeira correspondência em caso de duplicatas.
  • Assim como a função INDEX, a função MATCH também pode funcionar com faixas verticais e horizontais.
  • MATCH não diferencia maiúsculas de minúsculas.
  • Se o lookup_value da fórmula MATCH estiver na forma de texto, coloque-o entre aspas.
  • Se o lookup_value não foi encontrado no lookup_array, #N / D erro é retornado.

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)

correspondência de índice do excel 4

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

correspondência de índice do excel 5

O Í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)

(AD) Simplifique as pesquisas com Kutools: não é necessária digitação de fórmulas!

Kutools for Excel's Super pesquisa fornece uma variedade de ferramentas de pesquisa sob medida para atender todas as suas necessidades. Esteja você realizando pesquisas multicritério, pesquisando em várias planilhas ou pesquisando um para muitos, Super pesquisa simplifica o processo com apenas alguns cliques. Explore esses recursos para ver como Super pesquisa muda a maneira como você interage com os dados do Excel. Diga adeus ao incômodo de lembrar fórmulas complexas.

Ferramentas de pesquisa do Kutools

Kutools for Excel - Capacitando você com mais de 300 funções úteis para produtividade sem esforço. Não perca a chance de experimentá-lo com um teste gratuito completo de 30 dias! Comece agora!


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))

correspondência de índice do excel 6

Como funciona esta fórmula:
  • A primeira fórmula MATCH encontra a localização de Evelyn na lista A2:A11, fornecendo 5 como o número da linha para INDEX.
  • A segunda fórmula MATCH determina a coluna para as pontuações e retornos 3 como o número da coluna para INDEX.
  • A fórmula simplifica para =ÍNDICE(A2:C11,5,3), e INDEX retorna 90.

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))

correspondência de índice do excel 7

Observação: Você pode facilmente realizar uma pesquisa à esquerda para valores específicos usando o LOOKUP da direita para a esquerda característica de Kutools for Excel com apenas alguns cliques. Para implementar o recurso, navegue até o Kutools guia no seu Excel e clique em Super pesquisa > LOOKUP da direita para a esquerda no Fórmula grupo.

LOOKUP da direita para a esquerda

Se você não instalou o Kutools, clique aqui para baixe e obtenha um teste gratuito completo de 30 dias!


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:

=INDEX(array, MATCH(TRUE, EXACT(lookup_value, lookup_array), 0))
  • 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.

correspondência de índice do excel 8

Como funciona esta fórmula:
  • A função EXATA compara "JIMMY" com os valores da lista A2: A11, considerando o caso dos caracteres: Se as duas strings corresponderem precisamente, contabilizando caracteres maiúsculos e minúsculos, EXACT retornará VERDADEIRO; caso contrário, ele retorna FALSE. Como resultado, obtemos uma matriz contendo valores TRUE e FALSE.
  • A função MATCH então recupera o posição do primeiro valor TRUE na matriz, que deve ser 10.
  • Finalmente, INDEX recupera o valor no 10a posição fornecida por MATCH na matriz.

Observações:

  • Lembre-se de inserir a fórmula corretamente pressionando Ctrl + Shift + Enter, a menos que você esteja usando Excel 365 or Excel 2021, nesse caso, basta pressionar Entrar.
  • A fórmula acima pesquisa em uma única lista C2: C11. Se você quiser pesquisar em um intervalo com múltiplas colunas e linhas, digamos A2: C11, você deve oferecer números de colunas e linhas para INDEX:
  • =INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),3)
  • Nesta fórmula revisada, utilizamos a função MATCH para procurar por “JIMMY”, considerando o caso dos caracteres, no intervalo A2: A11, e assim que encontrarmos uma correspondência, recuperamos o valor correspondente do 3rd coluna do intervalo A2: C11.

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.

=INDEX(array, MATCH(MIN(ABS(lookup_array - lookup_value)), ABS(lookup_array - lookup_value),0))
  • 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.

Como funciona esta fórmula:
  • ABS(C2:C11-85) calcula a diferença absoluta entre cada valor no intervalo C2: C11 e 85, resultando em uma matriz de diferenças absolutas.
  • MÍN(ABS(C2:C11-85)) encontra o valor mínimo na matriz de diferenças absolutas, que representa a diferença mais próxima de 85.
  • A função MATCH MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0) então encontra a posição da diferença absoluta mínima na matriz de diferenças absolutas, que deve ser 10.
  • Finalmente, INDEX recupera o valor na posição da lista A2: A11 que corresponde à pontuação mais próxima de 85 no intervalo C2: C11.

Observações:

  • Lembre-se de inserir a fórmula corretamente pressionando Ctrl + Shift + Enter, a menos que você esteja usando Excel 365 or Excel 2021, nesse caso, basta pressionar Entrar.
  • Em caso de empate, esta fórmula retornará a primeira partida.
  • Para encontrar a correspondência mais próxima da pontuação médiasubstitua 85 na fórmula com MÉDIA(C2:C11).

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.

=INDEX(array, MATCH(1, (lookup_value1=lookup_array1) * (lookup_value2=lookup_array2) * (…), 0))

√ 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))

correspondência de índice do excel 9

Observações:

  • Nesta fórmula, evitamos codificar valores, simplificando a obtenção de uma pontuação com informações diversas, modificando os valores nas células G2, G3 e G4.
  • Você deve inserir a fórmula pressionando Ctrl + Shift + Enter exceto em Excel 365 or Excel 2021, onde você pode simplesmente pressionar Entrar.
    Se você constantemente se esquece de usar Ctrl + Shift + Enter para completar a fórmula e obter resultados incorretos, use a seguinte fórmula um pouco mais complexa, com a qual você pode completar com um simples Entrar chave:
    =INDEX(D2:D11,MATCH(1,INDEX((G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0,1),0))
  • As fórmulas podem ser complexas e difíceis de lembrar. Para simplificar pesquisas multicritério sem a necessidade de entrada manual de fórmulas, considere usar Kutools for Excel'S Pesquisa de várias condições recurso. Depois de instalar o Kutools, navegue até o Kutools guia no seu Excel e clique em Super pesquisa > Pesquisa de várias condições no Fórmula grupo.

    Pesquisa de várias condições

    Se você não instalou o Kutools, clique aqui para baixe e obtenha um teste gratuito completo de 30 dias!


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. correspondência de índice do excel 11
  4. Na caixa de diálogo pop-up, faça o seguinte:
    1. Clique no 1º ícone de correspondência de índice do excel 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º ícone de correspondência de índice do excel 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º ícone de correspondência de índice do excel 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. correspondência de índice do excel 12

Resultado

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

Observação: Para experimentar o Indexar e combinar em múltiplas colunas recurso, você precisará do Kutools for Excel instalado em seu computador. Se você ainda não instalou, não espere --- Baixe e instale agora para um teste gratuito de 30 dias sem limitações. Faça o Excel funcionar de maneira mais inteligente hoje!


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))

Observações:


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))

Observações:


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))

Como funciona esta fórmula:
  • MAX (C2: C11) procura o valor mais alto no intervalo C2: C11, o que é 96.
  • A função MATCH então encontra a posição do valor mais alto na matriz C2: C11, que deveria ser 1.
  • Finalmente, INDEX recupera o 1st valor na lista A2: A11.

Observações:

  • Caso haja mais de um valor máximo ou mínimo, como visto no exemplo acima onde dois alunos obtiveram a mesma pontuação mais alta, esta fórmula retornará a primeira correspondência.
  • Para determinar quem tem a pontuação mais baixa, use a seguinte fórmula:
    =INDEX(A2:A11,MATCH(MIN(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.

correspondência de índice do excel 15

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")

correspondência de índice do excel 16

Observações:

  • Você pode personalizar suas mensagens de erro substituindo "Não encontrado" com qualquer texto de sua escolha.
  • Se você quiser lidar com todos os erros, não apenas #N/A, considere usar o SE ERRO função em vez de IFNA:
    =IFERROR(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

    Observe que talvez não seja aconselhável suprimir todos os erros porque eles servem como alertas para possíveis problemas em suas fórmulas.

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.

Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations