Te ensinaremos agora as funções ÍNDICE e CORRESP no Excel. Este artigo trata passo a passo de como fazer estas junção de funções que vão te ajudar muito na hora de procurar valores no Excel.
Para fazer busca no Excel utilizamos muito o PROCV que é uma boa função, mas tem suas limitações. ÍNDICE + CORRESP e uma fórmula turbinada para procurar valores no Excel, ou seja, muito melhor que PROCV.
Assim que ler este artigo e aprender a usar estas funções você pode esquecer do PROCV.
Se preferir, temos um vídeo explicando tudo sobre usar ÍNDICE CORRESP no Excel. Veja em seguida o vídeo. Aproveita e se inscreve no nosso canal clicando aqui.
Vamos para ação então.
Para baixar a planilha utilizada nesta aula, preencha seu e-mail abaixo:
Primeiramente, vamos entender as funções separadamente.
Função corresp no Excel
A função CORRESP procura um item especificado em um intervalo de células e retorna a posição relativa desse item no intervalo. Ela vai retornar um número que indica a posição na lista indicada.
ÍNDICE e CORRESP no Excel juntas fazem muita diferença, mas vamos continuar a entender a CORRESP.
Por exemplo, veja na figura acima, onde marcamos com uma borda vermelha o intervalo C3:C12. Neste espaço marcado são 10 linhas. Se eu quiser saber em qual posição corresponde ao valor “11003” utilizando a função CORRESP ela vai retornar a valor “4”, pois este valor está na quarta linha da sequência.
Neste caso utilizaríamos a seguinte fórmula: =CORRESP(11003;C3:C12;0). Abaixo o resultado na nossa planilha.
Sintaxe da função CORRESP
CORRESP(valor_procurado, matriz_procurada, [tipo_correspondência])
- valor_procurado: Obrigatório. O valor que você deseja corresponder na parte da matriz_procurada. No exemplo o valor “11003”. O valor_procurado pode ser um valor (número, texto ou valor lógico) ou uma referência de célula.
- matriz_procurada: Obrigatório. O intervalo de células que estão sendo pesquisadas.
- tipo_correspondência: Opcional. O número -1, 0 ou 1. Este argumento indica como o Excel faz a correspondência de valor_procurado a valores na matriz_procurada. -1 (É menor do que), busca o menor valor mais próximo do valor procurado. 0 (Correspondência exata), busca sempre o valor exato ao procurado e 1 (É maior do que), busca o maior valor mais próximo do valor procurado.
Veja no vídeo em seguida tudo sobre a função CORRESP. Detalhamos bem esta função no nosso canal do Youtube.
Seguindo e falando mais da estratégia ÍNDICE + CORRESP no Excel, vamos em seguida explicar a função ÍNDICE.
fUNÇÃO ÍNDICE
A função ÍNDICE retorna um valor ou a referência a um valor de dentro de uma tabela ou intervalo.
Há duas maneiras de usar a função ÍNDICE, se você deseja retornar o valor de uma célula especificada ou uma matriz de células, esta é a forma matricial. Se você deseja retornar uma referência a células especificadas, deve seguir o Formulário de referência.
Nosso exemplo será feito pela forma matricial, que é a mais usada nesta função. Caso queira saber mais sobre a outra forma é só clicar no link acima.
Essa forma retorna o valor de uma tabela ou uma matriz, selecionado pelos índices de número de linha e coluna.
Sintaxe da função:
ÍNDICE(matriz; núm_linha; [núm_coluna])
- matriz : Obrigatório. Um intervalo de células ou uma constante de matriz.
- núm_linha: Seleciona a linha na matriz a partir da qual um valor deverá ser retornado. Se núm_linha for omitido, núm_coluna será necessário.
- núm_coluna: Seleciona a coluna na matriz a partir da qual um valor deverá ser retornado. Se núm_coluna for omitido, núm_linha será necessário.
Então, entenda melhor no exemplo a seguir.
Vamos analisar a fórmula: =ÍNDICE(B3:E12;3;4).
A matriz, ou seja, o conjunto de células selecionadas é o intervalo B3:E12. Isto indica que a função vai buscar aqui. O núm_linha é “3”, está buscando na terceira linha da matriz. E por fim o núm_coluna é “4”, retornando o valor da quarta coluna. Na matriz a linha 3 com a coluna 4 é o valor “17”. É esse o valor que vemos na figura em seguida.
Para saber mais sobre a função ÍNDICE, então veja o vídeo abaixo. Detalhamos bem esta função no nosso canal do Youtube.
Agora que entendemos bem sobre cada uma das funções, vamos entender de fato a fórmula com ÍNDICE + CORRESP no Excel.
índice e corresp no Excel
A função ÍNDICE, por sim só, é a função que faz a pesquisa e busca dos valores. Você observou que a função CORRESP retorna um número onde está o valor que buscamos. Utilizando a duas juntas automatizamos o processo de busca.
Então podemos usar a função CORRESP para buscar o local da linha ou da coluna. Vamos para os exemplos.
No quadro acima utilizamos a função ÍNDICE e CORRESP juntas para buscar o nome do produto, que está na coluna “Item”. Observe a fórmula em seguida.
=ÍNDICE(B3:E12;CORRESP(G3;C3:C12;0);1)
A matriz de procura do ÍNDICE é o intervalo “B3:E12”, ou seja, é onde a fórmula está procurando o nome do produto. A referência de linha desta função foi representada pela função CORRESP, representada assim: CORRESP (G3;C3:C12;0).
Nesta função o valor procurado é o que está na célula “G3”, no caso o código “11001”. Ela procura dentre as células C3:C12 em qual posição está o código “11001”, que é na segunda posição, então vai retornar 2 para a linha. Observem o 0 no final da função CORRESP que diz que buscamos pela referência exata.
Na parte da fórmula da função ÍNDICE diz qual a coluna, colocamos diretamente o 1, ou seja a primeira coluna da matriz. Então na matriz buscamos o valor que está na linha 2 na coluna 1, no caso, “Produto XYZ”.
Veja o resultado na figura abaixo.
Lembre-se que se fossemos usar a PROCV aqui, não teria como ser na estrutura que está a tabela, já que ela não busca para a esquerda, somente para a direita.
Deixando a coluna também automática
Vamos agora colocar mais um CORRESP na fórmula na parte onde determina a coluna a ser buscada na função ÍNDICE. Assim podemos deixar essa parte automática também.
Mais um ótimo exemplo de utilização da ÍNDICE + CORRESP no Excel. Vamos lá.
=ÍNDICE(B3:E12;CORRESP(G3;C3:C12;0);CORRESP(I2;B2:E2;0))
Veja que tem um CORRESP, onde antes colocamos o “1” para determinar a coluna de busca.
Esta assim, CORRESP(I2;B2:E2;0). A célula “I2” está escrito “Preço”, então este é o valor procurado. Estamos procurando na matriz “B2:E2”, onde contem os nomes das colunas da nossa tabela. O “Preço” está na quarta coluna, ou seja, será retornado aqui o número 4.
Então a função ÍNDICE vai buscar neste exemplo a linha 2 e a coluna 4, onde temos o número “12”. Veja abaixo o resultado da fórmula.
Top, não é!?
Algumas vezes pode ser que você precise travar as células para copiar a fórmula na planilha. Leia este artigo para saber tudo sobre travar células no Excel: Como Travar Células no Excel.
Viu com é tranquilo de usar as funções ÍNDICE e CORRESP no Excel para buscar valores nas suas tabelas?
É só seguir esse passo a passo que será sucesso para você.
Se tiver alguma dúvida que não respondemos aqui, é só deixar seu comentário que a gente responde. Até a próxima.