Índice Corresp Excel – Como utilizar as funções Índice e Corresp

Erro For Each - Clique para ampliar
ERRO NO FOR EACH DO EXCEL 2010
30 de julho de 2010
Caminho do Arquivo
ABRIR ARQUIVO TEXTO COM MAIS DE 1 MILHÃO DE LINHAS NO EXCEL
1 de agosto de 2010

Objetivo: As funções Índice e Corresp são muitas vezes utilizadas em conjunto e a sua utilização é muito semelhante ao PROCV.

Iremos neste artigo demonstrar como funcionam separadamente e juntas.

Vamos ver primeiro como elas funcionam separadamente.

  • Índice

A função índice retorna o valor que se encontra em determinada linha e coluna de uma matriz de dados como por exemplo =ÍNDICE(A2:G618;3;6). Esta função retorna o valor que se localiza entre as células A2 e G618 na coluna 3 e linha 6.

  • Corresp

Esta função retorna a posição de um determinado valor em uma linha ou uma coluna. Esta função não pode ser utilizada em uma com mais de uma linha ou coluna.

Exemplo de uso:  =CORRESP(J3;A1:A618;0) sendo: J3 o valor que está sendo procurado, A1:A618 o intervalo de dados dentro da coluna A em que se deseja localizar o valor, 0 que se deseja a correspondência exata.

  • Índice+Corresp

Como verificamos o Índice retorna o valor de uma célula em uma determinada posição e o Corresp determina a posição de um valor dentro de um intervalo de dados, logo eles são usados da seguinte forma juntos:

=ÍNDICE(A2:G618;CORRESP(358;A2:A618;0);CORRESP(“Vendedor”;A1:G1;0)), nesta fórmula temos em: A2:G618 a definição do intervalo de células em que se deseja localizar um valor, 358 é o valor a se localizar na coluna A2:A618, Vendedor é a coluna que se deseja localizar entre o intervalo de colunas A1 e G1.

Desta forma temos que o índice determina o local aonde será realizada a pesquisa, e os corresp, identificam a linha e a coluna que se deseja identificar no intervalo. [saiba_mais]

Então é isso, o download do arquivo deste post pode ser bastante elucidativo, por isso não deixe de baixar se houverem dúvidas.

DIGITE O SEU EMAIL PARA FAZER O DOWNLOAD DOS ARQUIVOS:

Abraço

Marcos Rieper

See this post in English: http://guideexcel.com/excel-index-match/


Clique aqui e leia mais sobre Excel VBA. https://www.guiadoexcel.com.br/vba/ O Guia do Excel foi criado por Marcos Rieper e oferece artigos, dicas, tutoriais e modelos de planilhas prontas. Aqui você encontra tudo sobre Excel, seja de nível básico, intermediário,  avançado e VBA. O Guia do Excel oferece diversos materiais completamente gratuitos para download. Navegue em nosso site e confira! Conheça também a nossa Loja do Excel https://loja.guiadoexcel.com.br/
Cursos

Curso Excel Completo – Curso Excel Básico + Curso Excel Avançado – Acesso Vitalício

R$218,00 R$179,00

COMPRAR
Cursos

Curso Excel Master – Curso Excel Básico + Curso Excel Avançado + Curso VBA Excel + LP – Acesso Vitalício

R$357,00 R$249,00

COMPRAR
Cursos

Curso Excel PRO – Curso Excel Avançado + Curso VBA Excel + Lógica de programação – Acesso Vitalício

R$258,00 R$199,00

COMPRAR
Cursos

Curso Excel Web – Curso VBA Excel + Lógica de programação + Curso Web Scraping VBA- Acesso Vitalício

R$388,90 R$309,00

COMPRAR

46 Comentários

  1. Fábio disse:

    olá!!

    esse indice é para retornar o valor da venda, correto??? não está funcionando, ou nao entendi!!!

    • rieperexcel disse:

      Olá Fábio,

      Com relação á sua dúvida o índice no exemplo retorna sim o valor do pedido, só que no exemplo ele é fixo, não variando conforme a linha e a coluna, o que faz ele variar a coluna é a utilização do índice com o corresp conforme o campo J9 da planilha.

      Obrigado pela sua visita, o convido a voltar sempre e deixar suas dúvidas e sugestões.

      At.

      Rieper

  2. Mauro disse:

    Olá, essa formula quase faz o que eu preciso.

    Ela pega uma referencia com o corresp e procura dentro do campo delimitado pelo indice.

    Mas eu preciso de uma formula que pegue 2 referecias detro do indice.
    Ex: eu tenho varias linhas com as marcas dos produtos. (nestle, Garoto, Arcor, etc) e varias colunas com os produtos (barra de chocolate, caixa de bom bom, brindes, etc).
    Eu gostaria a formula busque o preço da celula.
    Para isso eu devo informar a marca e a linha de produto.
    Essa função indice pode fazer isso ?

    • Marcos Rieper disse:

      Boa tarde Mauro,

      Uma forma de se fazer isso é utilizando uma chave composta, neste caso você pode utilizar =A1&”-“&B1 por exemplo, formando desta forma uma chave única, que é o que você precisa.

      Repita a operação no lugar aonde você quer buscar estes dados, e vai funcionar.

      Abraço

      Marcos Rieper

    • VInicius disse:

      Você pode Concatenar as 2 informações que você vai utilizar para busca, Exemplo Coluna A (Marca), Coluna B (Produto) use a formula para Concatenar essa informação e Utilize a =Índice(Tabela de preço+informações concatenadas;CORRESP(Valor Procurado;Tabela onde Procurar;0);1) esse 1 é a posição da coluna que você deseja retornar como resposta no seu caso a coluna que se encontra o Preço.

      EX: =ÍNDICE(C2:D8;CORRESP(H3;D2:D8;0);1)

  3. Mauro disse:

    Não consegui, existe algum artigo ensianado a ultilização dessa chave composta ?

  4. SUELI disse:

    Eu queria saber como posso achar uma informação de uma tabela, correspondente a outra em outra tabela. Por exemplo, eu tenho um banco com os municípios do ESP. Eu preciso cruzar a tabela de dados por município com outra onde consta a região administrativa onde o município se encontra. Ou seja, eu quero acrescentar as regiões administrativas à primeira tabela. Não sei como fazer. se alguém souber, agradeço.
    Sueli

  5. Luan disse:

    Olha, com esta formula eu consigo buscar o total de produtos vendidos por funcionario?

    Tipo:

    | VENDEDOR | PRODUTO | TOTAL
    | JOAO | LARANJA | 2
    | PEDRO | ACEROLA | 3
    | JOAO | MAÇA | 5

    Nesse caso joao vendeu 7 produtos
    teria como obter o total dos produtos buscando pelo nome por exemplo?

  6. RUDl..HARDT disse:

    BON….DlA…NÃO….ENCONTREl…A….TABELA…DO…BRASElRÃO…DAS…SERlES..A.B.e..C

    OBRlGADO

    RUDl

  7. Eduardo disse:

    Bom dia!

    Estou com um problema. Em uma planilha, possuo vários códigos de SAP correspondentes a diferentes clientes. Em outra, tenho 5 colunas onde esses códigos se “espalham”, ou seja, podem estar na coluna 1 ou 3 ou nas duas. O que eu preciso é procurar esses códigos dentro destas cinco colunas e trazer o valor correspondente à quinta coluna (que são os valores que eu efetivamente preciso). Há alguma fórmula que eu consiga fazer isso?

  8. EDUARDO disse:

    ótima ajuda !!!! valew vcs sempre me ajudando !!!!

    liberdadeeeeeeee

  9. elaine disse:

    Numa única tabela tenho 20 colunas com os Estados de destino e 20 linhas com os mesmos Estados de origem e busco encontrar a alíquota de ICMS correspondente a cada origem e destino, qual seria a fórmula?

  10. Michel disse:

    Bom dia,

    Estou montando duas tabelas que terão correspondência uma com as outras. Pois bem, na primeira, tenho um campo com o tipo de despesa onde usei validação de dados. Ao selecionar o tipo de despesa, gostaria que na coluna ao lado aparecesse um código de lançamento (que está na tabela ao lado, junto com os dados utilizados para a validação de dados). Qual a melhor opção de se fazer isso? Pelo PROC (H ou V) ou em ÍNDICE e CORRESP?

    Obrigado.

  11. Diego disse:

    Sensacional a ajuda. Muito bom o artigo.

  12. Luciano disse:

    Amigo esse valor 358 pode ser um dado de entrada???Eu gostaria que o usuário pudesse fazer assim, digite o pedido, depois digite o usuário e ele retornasse o valor cliente!!!

    • Marcos Rieper disse:

      Bom dia Luciano,

      No exemplo em anexo do artigo não é necessário que seja realizado a pesquisa pelo cliente e pelo pedido, pois a chave dele já é o pedido, um número único que identifica a linha.

      Desta forma você consegue alterar também as informações somente mudando o campo logo abaixo pela combobox e assim retornar o cliente.

      Abraço

      Marcos Rieper

  13. Luciano disse:

    Muito Obrigado Marcelo Rieper!!!!!consegui fazer minha planilha!!!!!!!!

  14. A função corresp sempre pesquisa de “cima para baixo” existe a possibilidade de pesuisar “de “baixo para cima”?

  15. Eu já resolvi invertendo a posição dos itens na lista.

    Na planilha que estou montando precisava descobrir numa relação contendo apenas zero e um, qual a posição do primeiro um a partir da parte de baixo da lista.

  16. rogerio disse:

    Bom dia,
    eu estou com uma planilha com as seguintes formulas
    indice(Corresp

    nessa formula eu quero identificar o item lançado e mostrar sua quantidade, mas encontrei um problema, quando eu lanço itens respectivamente iguais, ele não soma a quantidade deles, ele retorna apenas a quantidade do primeiro item

    poderia me ajudar com essa formula?
    https://docs.google.com/file/d/0B9OpNQNUHrV5dEJSN2thSl9pTW8/edit?usp=sharing

  17. Rogerio G. disse:

    Marcos,
    realmente deu certo essa formula somases

    muito obrigado!

  18. Julivan disse:

    Bom dia,

    Como faço para procurar 2 condições? No caso vc procurou o número do pedido. E se eu quisesse procurar, por exemplo, o vendedor que atendeu o cliente X com vencimento na data Y?

    Obrigado.

  19. Miguel disse:

    Bom dia!

    Muito boa explicação.. Gostaria de saber apenas como é possível eu somar duas colunas com o mesmo nome, ou no meu caso, a mesma data.. Pois, pelo que pude testar, ao inserir uma célula igual a anterior na matriz, ele retoma apenas a primeira coluna.. como se fosse um PROCV.

    Abs

  20. Tiago disse:

    Boa tarde, Pessoal tenho a tabela abaixo:

    1º PARCELA (valor)R$ 191,65 (data)10/09/2011 (índice 1)46,007257 (índice 2)54,061280 (Valor Atualizado) R$ 225,20 12,00% 26/05/2014 989 R$ 74,69 R$ 299,89

    E a Tabela

    Mês/Ano Índice
    jan/05 32,957268

    Como eu faço a atualização automática para alteração de datas

    Já tentei isso: =ÍNDICE(‘[Tabela Atualização TJSP.xlsx]Plan1’!$A$1:$B$115; corresp mas nada deu certo.

    Alguém pode me ajudar?

  21. […] Veja este artigo em português: http://guiadoexcel.com.br/indice-corresp […]

  22. Paulo disse:

    Estou com um problema onde aplique a seguinte formula
    Primeira condição

    =SE(ÉERROS(ÍNDICE(ITEN_MODELOS!$E$2:$E$40000;(CORRESP($X2&BC$3&$AE2;ITENS_MODELOS!$D$2:$D$40000&ITENS_MODELOS!$E$2:$E$40000&ITENS_MODELOS!$J$2:$J$40000;0))));””;ÍNDICE(ITEN_MODELOS!$E$2:$E$40000;(CORRESP($X2&BC$3&$AE2;ITENS_MODELOS!$D$2:$D$40000&ITENS_MODELOS!$E$2:$E$40000&ITENS_MODELOS!$J$2:$J$40000;0))))

    Onde a célula X e BC correrá apenas na vertical e fixa na horizontal e AE correra na horizontal e fixa na vertical isso para um range de 700 colunas e 40000 linhas
    O meu problema é que colocando nessa condição o Excel fica muito lento para processar, gostaria da sua ajuda para transformar isso em VBA

    Segunda condição

    =SE(ÉERROS(SOMASES(ITENS_MODELOS!$F$2:$F$40000;ITENS_MODELOS!$D$2:$D$40000;’ITENS AS-400′!$X2;ITENS_MODELOS!$E$2:$E$40000;’ITENS AS-4000′!PX2)*NV$1);0;SOMASES(ITENS_MODELOS!$F$2:$F$40000;ITENS_MODELOS!$D$2:$D$40000;’ITENS AS-400′!$X2;ITENS_MODELOS!$E$2:$E$40000;’ITENS AS-4000′!PX2)*NV$1)

    Onde a célula X correrá apenas na vertical e fixa na horizontal e PX correra na horizontal e na vertical isso para um range de 700 colunas e 40000 linhas

    O meu problema é que colocando nessa condição o Excel fica muito lento para processar, gostaria da sua ajuda para transformar isso em VBA

    • Marcos Rieper disse:

      Boa noite Paulo,

      Está extremamente pesado porque primeiro o Excel encontra em uma lista de 700, depois uma lista de 4000, multiplicando isso pela quantidade de vezes que utiliza a função na sua planilha.

      Utiliza também o Somases que é uma função matricial interna do Excel, muito pesada.

      Você pode utilizar um banco de dados para realizar esta tarefa, armazenando as informações nele e buscando através de consultas prontas, mas para isso será necessário ter conhecimento para tal.

      Abraço

      Marcos Rieper

  23. Karla disse:

    Olá, estou adorando o site, muito informativo.
    Será que poderia me ajudar com meu desafio?!
    Montei uma planilha de contas a pagar.
    Na primeira linha, eu digito todos os dados e através de uma macro, salvo e ordeno a listagem que se forma.

    O problema é: como não causar duplicidade de entrada?!
    Preciso de uma fórmula que pesquise e me informe a linha, caso encontre CNPJ e valor iguais aos que estou dando entrada. Até aqui eu consegui… o que eu não consigo é deixar isso bonito e apresentável, ex: Item cadastrado na linha —- / Item não cadastrado. SE(B9=””;””;SE(CORRESP(E9&K9;E13:E5000&K13:K5000;0);”CADASTRADO NA LINHA” CORRESP(E9&K9;E13:E23&K13:K23;0);”EFETUAR CADASTRO”))

    QUASE deu certo!

    • Marcos Rieper disse:

      Boa noite Karla,

      No lugar aonde você coloca na fórmula E5000&K13:K5000 para pesquisar não vai funcionar, dado que é uma lista de dados única, mas você pode criar uma nova coluna ao lado de K na planilha e colocar E1&K1 e arrastar, e depois utilizar como base esta coluna para pesquisar. Inclusive pode usar o PROCV, não é nem necessário utilizar o índice, corresp.

      Veja: http://guiadoexcel.com.br/procv-e-proch-excel

      Abraço

      Marcos Rieper

  24. Nivaldo disse:

    Por favor estou fazendo um trabalho e preciso de uma formula que calcule a somatoria do valor informado;
    a1=22
    a2= (a1=22 ou seja 2+2=4, o valor a2 devera ser 4)

    por favor me ajude
    grato
    Nivaldo

  25. Jair disse:

    ola,

    da pra fazer indice corresp, em planilha salva no google docs?

  26. Joabson Silva disse:

    Amigo! isso era o que eu procurava a tanto tempo! salvou a minha vida! Obrigado!

  27. franciane disse:

    Bom dia,
    Preciso de ajuda tenho uma planilha, na qual eu tenho 2 colunas – defeito / quantidade tem umas 50 linhas, como eu precisada das 10 maiores quantidades, fiz outra tabela, com a função maior, achei tudo certinho, as 10 maiores quantidade…. Agora preciso colocar o defeito do respectivo valor, uso a função indice + corresp (+ a formula SE, porque precisava que se o valor fosse 0, a célula ficasse vazia), função funciona corretamente, porém quando o valor repete ele me mostra a 1 opção do defeito da lista.
    EX.
    Códigos corretos
    amassado – 10
    quebrado – 6
    trincado- 6
    raxado – 3

    quando uso a funcão, índice + corresp, o que me mostra.
    Qnt / defeito
    10 – amassado
    6 – quebrado
    6 – quebrado
    3 – trincado

    *preciso que ele me mostre o defeito ‘trincado no lugar de quebrado’ para minh planilha ficar certa.

    Pode me ajudar. Agradeço.

  28. Marcelo Miacci disse:

    Olá Marcos, boa tarde… Estou criando uma planilha com as funções ÍNDICE e CORRESP e segui a risca o exemplo que você deu nesta aula, porém, tem alguma coisa errada e eu não estou conseguindo identificar o erro para corrigi-lo. Poderia me ajudar?
    Os dados são os seguintes:
    1. No intervalo B1 a G1 estão os nomes dos itens que tenho que controlar: Despesas, Receitas, Resultados, Gerenciais, Desp. Fixas e Despesas Operacionais Variáveis.
    2. No intervalo A2 a A13 estão os períodos – meses do ano
    3. No intervalo B2 a G13 estão os valores correspondentes
    4. Na célula J3 especifico o mês a ser pesquisado (lista suspensa)
    5. Na célula J4 especifico o item a ser pesquisado (lista suspensa)

    Estou utilizando a formula =ÍNDICE(A2:G13;CORRESP(J3;A2:A13;0);CORRESP(J4;A1:G1;0))
    Funciona perfeitamente, com exceção da última coluna (Despesas Operacionais Variaveis), onde me retorna #N/D. Todas as buscas me retornam exatamente o valor correspondente à célula especificada, menos os relacionados a esta coluna. Já verificaquei a ortografia e está tudo ok…
    O que está acontecendo?
    Obrigado.
    Marcelo Miacci

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.