Note: The other languages of the website are Google-translated. Back to English
Log in  \/ 
x
or
x
Inscreva-se  \/ 
x

or

Como usar INDEX e MATCH juntos no Excel

Ao trabalhar com tabelas do Excel, você pode encontrar constantemente situações em que precisa pesquisar um valor. Neste tutorial, mostraremos como aplicar a combinação das funções INDEX e MATCH para fazer pesquisas horizontais e verticais, pesquisas bidirecionais, pesquisas com distinção entre maiúsculas e minúsculas e as pesquisas que atendem a vários critérios.

O que as funções INDEX e MATCH fazem no Excel

Como usar as funções INDEX e MATCH juntas


O que as funções INDEX e MATCH fazem 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.

Uso da 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 column_num esteja presente) refere-se ao número da linha da matriz.
  • column_num (opcional, mas obrigatório se row_num for omitido) refere-se ao número da coluna da matriz.

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

= ÍNDICE (E2: E11, 6) >>> retorna 60

correspondência de índice do excel 01

√ Nota: o intervalo E2: E11 é onde o exame final é listado, 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 (B2: E2,3), qual valor ele retornará? --- Sim, ele vai voltar China, pela 3rd valor no intervalo fornecido.

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 o país de onde vem a emily com INDEX, podemos localizar o valor com um número de linha 8 e um número de coluna 3 nas células de B2 a E11 desta forma:

= ÍNDICE (B2: E11,8,3) >>> retorna China

correspondência de índice do excel 02

De acordo com os exemplos acima, sobre a função INDEX no Excel, você deve saber que:

  • A função INDEX pode funcionar com faixas verticais e horizontais.
  • A função INDEX não faz distinção entre maiúsculas e minúsculas.
  • O número da linha vai à frente do número da coluna (se você precisar de ambos os números) na fórmula INDEX.

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.

Agora, vamos aprender sobre os fundamentos da função MATCH primeiro.


Uso da 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_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:

= CORRESPONDÊNCIA ("vera", C2: C11,0) >>> retorna 4

correspondência de índice do excel 03

√ Nota: A função MATCH não faz distinção entre maiúsculas e minúsculas. O resultado “4” indica que o nome “Vera” está na 4ª posição da lista. O “0” na fórmula é o tipo de correspondência que encontrará o primeiro valor na matriz de pesquisa que se iguala exatamente ao valor de pesquisa “Vera”.

Saber a posição da pontuação “96” na linha de B2 a E2, você pode usar o MATCH assim:

= CORRESPONDÊNCIA (96, B2: E2,0) >>> retorna 4

correspondência de índice do excel 04

☞ 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 também não faz distinção entre maiúsculas e minúsculas.
  • Se o valor de pesquisa da fórmula MATCH estiver na forma de texto, coloque-o entre aspas.

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 usar as funções INDEX e MATCH juntas

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

Exemplo para combinar INDEX e MATCH

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

Por exemplo, para saber Pontuação final do exame de Evelyn, devemos usar a fórmula:

=INDEX(A2:D11,MATCH("evelyn",B2:B11,0),MATCH("final exam",A1:D1,0)) >>> retorna 90

correspondência de índice do excel 05

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

correspondência de índice do excel 06

Como você pode ver acima, o grande ÍNDICE fórmula contém três argumentos:

  • ordem: A2: D11 diz a INDEX para retornar o valor correspondente das células por meio de A2 a D11.
  • row_num: MATCH ("evelyn", B2: B11,0) diz a INDEX a linha exata do valor.
  • Sobre a fórmula MATCH, podemos explicá-la como: retornar a posição do primeiro valor que é exatamente igual a “evelyn” nas células de B2 a B11 em um valor numérico, que é 5.
  • column_num: MATCH ("exame final", A1: D1,0) diz a INDEX a coluna exata do valor.
  • Sobre a fórmula MATCH, podemos explicá-la como: retornar a posição do primeiro valor que é exatamente igual a “exame final” nas células de A1 a D1 em um valor numérico, que é 4.

Então, você pode ver a grande fórmula tão simples quanto a que mostramos abaixo:

= INDEX (A2: D11,5,4)

No exemplo, usamos valores codificados, "evelyn" e "exame final". No entanto, em uma fórmula tão grande, não queremos valores codificados, pois teremos que alterá-los cada vez que procurarmos por algo novo. Nessas circunstâncias, podemos usar referências de células para tornar a fórmula dinâmica assim:

= INDEX (A2: D11,PARTIDA(G2, B2: B11,0),PARTIDA(F3, A1: D1,0))

correspondência de índice do excel 07


INDEX e MATCH para aplicar uma pesquisa à esquerda

Agora, digamos que você precise saber a classe de Evelyn, como podemos usar INDEX e MATCH para saber a resposta? Se você prestar atenção, deverá notar que a coluna da classe está no lado esquerdo da coluna do nome, e isso está além da capacidade de outra função de pesquisa poderosa do Excel, a VLOOKUP.

Na verdade, a capacidade de pesquisa à esquerda passa a ser um dos aspectos em que a combinação de INDEX e MATCH é superior a VLOOKUP.

Saber Aula de Evelyn, tudo o que você precisa fazer é alterar o valor na célula F3 para "Classe" e usar a mesma fórmula mostrada acima, as funções INDEX e MATCH lhe dirão a resposta imediatamente:

=INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0)) >>> retorna A

correspondência de índice do excel 08

Se você instaloulED Kutools for Excel, um suplemento profissional do Excel desenvolvido pela nossa equipe, você também pode aplicar uma pesquisa à esquerda para valores específicos com seu LOOKUP da direita para a esquerda recurso com poucos cliques. Para implementar o recurso, vá para o Kutools guia em seu Excel, encontre o Fórmula grupo e clique LOOKUP da direita para a esquerda na lista suspensa de Super LOOKUP. Você verá uma caixa de diálogo pop-up como esta:

correspondência de índice do excel 09

Clique aqui para obter etapas concretas para aplicar o recurso de pesquisa à esquerda com o Kutools para Excel.


INDEX e MATCH para aplicar uma pesquisa bidirecional

Agora, você é capaz de fazer a fórmula de combinação INDEX e MATCH com valores de pesquisa dinâmica para aplicar pesquisas bidirecionais? Vamos praticar a criação de fórmulas nas células G3, G4 e G5 conforme mostrado abaixo:

correspondência de índice do excel 10

Aqui estão as respostas:

Célula G3: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0))
Célula G4: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F4,A1:D1,0))
Célula G5: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F5,A1:D1,0))

√ Nota: Depois de aplicar as fórmulas, você pode obter facilmente as informações de qualquer aluno, alterando o nome na célula G2.


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

A partir dos exemplos acima, sabemos que as funções INDEX e MATCH não diferenciam maiúsculas de minúsculas. No entanto, nos casos em que você precisa de sua fórmula para distinguir caracteres maiúsculos e minúsculos, você pode adicionar EXATO funcionar para suas fórmulas como esta:

=INDEX(return_range,MATCH(TRUE,EXACT("lookup_value1",range1),0),MATCH("lookup_value2",range2,0))
√ Nota: Esta é uma fórmula de matriz que requer que você insira com Ctrl + Shift + Enter. Um par de chaves aparecerá na barra de fórmulas.

Por exemplo, para saber Pontuação do exame do JIMMY, use as funções como estas:

correspondência de índice do excel 11

=INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),MATCH("final exam",A1:C1,0)) >>> retorna 86

Ou você pode usar referências de células:

=INDEX(A2:C11,MATCH(TRUE,EXACT(F2,A2:A11),0),MATCH(E3,A1:C1,0)) >>> retorna 86
Nota: Não se esqueça de entrar com Ctrl + Shift + Enter.


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

Ao lidar com um grande banco de dados com várias colunas e legendas de linha, é sempre difícil encontrar algo que atenda a várias condições. Nesse caso, consulte a fórmula abaixo para pesquisar vários critérios:

=INDEX(return_range,MATCH(1,(lookup_value1=range1)*(lookup_value2=range2)*(…),0))
Nota: Esta é uma fórmula de matriz que requer que você insira com Ctrl + Shift + Enter. Um par de chaves aparecerá na barra de fórmulas.

Por exemplo, para encontrar o nota final do exame de Coco, da Classe A, que é da Índia, a fórmula é a seguinte:

correspondência de índice do excel 12

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0)) >>> retorna 88
√ Nota: Não se esqueça de entrar com Ctrl + Shift + Enter.

Bem, e se você constantemente se esquece de usar Ctrl + Shift + Enter completar a fórmula para que ela retorne resultados incorretos? Aqui temos uma fórmula mais complexa, com a qual você pode completar com um simples Entrar chave:

=INDEX(return_range,MATCH(1,INDEX((lookup_value1=range1)*(lookup_value2=range2)*(…),0,1),0))

Para o mesmo exemplo acima para encontrar o nota final do exame de Coco, da Classe A, que é da Índia, a fórmula que só precisa de um Entrar hit é o seguinte:

correspondência de índice do excel 13

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

Aqui, não usaremos valores codificados permanentemente, pois queremos uma fórmula universal no caso de vários critérios. Somente desta forma, podemos facilmente obter o resultado que queremos alterando os valores nas células G2, G3, G4 no exemplo acima.

Com Kutools for Excel,s Recurso de pesquisa de várias condições, você pode pesquisar valores específicos com vários critérios em poucos cliques. Para implementar o recurso, vá para o Kutools guia em seu Excel, encontre o Fórmula grupo e clique Pesquisa de várias condições na lista suspensa de Super LOOKUP. Você verá uma caixa de diálogo pop-up conforme mostrado abaixo:

correspondência de índice do excel 14

Clique aqui para obter etapas concretas para aplicar o recurso de pesquisa de várias condições com o Kutools para Excel.


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

Se tivermos uma planilha Excel com diferentes colunas compartilhando uma legenda conforme mostrado abaixo, como podemos combinar o nome de cada aluno com sua classe com INDEX e MATCH?

correspondência de índice do excel 15

Aqui, deixe-me mostrar a você como completar a tarefa com nossa ferramenta profissional Kutools for Excel. Com a sua Auxiliar de Fórmula, você pode combinar rapidamente os alunos com suas turmas, conforme as etapas mostradas abaixo:

1. Selecione a célula de destino onde deseja aplicar a função.

2. Debaixo de Kutools guia, vá para Fórmula Helper, clique em Fórmula Helper na lista suspensa.

correspondência de índice do excel 16

3. Escolher Pesquisa do tipo de fórmula, então clique em Indexar e combinar em várias colunas.

correspondência de índice do excel 17

4. a. Clique no primeiro ícone de correspondência de índice do excelbotão no lado direito de Lookup_col para selecionar as células das quais deseja retornar um valor, ou seja, os nomes das classes. (Você só pode selecionar uma única coluna ou linha aqui.)
    b. Clique no segundo ícone de correspondência de índice do excelbotão no lado direito de Table_rng para selecionar as células para corresponder aos valores no selecionado Lookup_col, ou seja, os nomes dos alunos.
    c. Clique no terceiro ícone de correspondência de índice do excelbotão no lado direito de Valor_procurado para selecionar a célula a ser pesquisada, ou seja, o nome do aluno que deseja coincidir com sua turma.

correspondência de índice do excel 18

5. Clique em Ok, você verá o nome da classe de Jimmy mostrado na célula de destino.

correspondência de índice do excel 19

6. Agora você pode arrastar a alça de preenchimento para baixo para preencher as classes de outros alunos.

correspondência de índice do excel 20

Clique para baixar o Kutools para Excel para um teste gratuito de 30 dias.



  • Super Formula Bar (edite facilmente várias linhas de texto e fórmula); Layout de leitura (ler e editar facilmente um grande número de células); Colar na faixa filtrada...
  • Mesclar células / linhas / colunas e manutenção de dados; Dividir o conteúdo das células; Combine Linhas Duplicadas e Soma / Média... Evite células duplicadas; Comparar intervalos...
  • Selecione Duplicado ou Único Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros; Seleção aleatória ...
  • Cópia exata Várias células sem alterar a referência da fórmula; Criação automática de referências para várias folhas; Inserir marcadores, Caixas de seleção e mais ...
  • Fórmulas favoritas e de inserção rápida, Intervalos, gráficos e imagens; Criptografar células com senha; Criar lista de discussão e enviar emails ...
  • Extrair Texto, Adicionar texto, remover por posição, Remover Espaço; Criar e imprimir subtotais de paginação; Converter entre conteúdo de células e comentários...
  • Super Filtro (salvar e aplicar esquemas de filtro a outras planilhas); Classificação Avançada por mês / semana / dia, frequência e mais; Filtro Especial por negrito, itálico ...
  • Combine pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Divida os dados em várias folhas; Conversão em lote de xls, xlsx e PDF...
  • Agrupamento de tabela dinâmica por número da semana, dia da semana e mais ... Mostrar células desbloqueadas, bloqueadas por cores diferentes; Destacar células que possuem fórmula / nome...
guia kte 201905
  • Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
officetab bottom
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.