Neste artigo, aprenderemos como usar as funções INDEX e MATCH para pesquisar valor no Excel.
Função INDEX & MATCH para substituir a função vlookup
Normalmente usamos a função PROCV para encontrar um valor sabendo o índice da linha e o número da coluna. Para a função VLOOKUP, precisamos ter o número da coluna e os critérios de correspondência de linha e, o mais importante, o valor a descobrir está à direita do valor de correspondência. Mas os dados nem sempre suportam isso. Muitas vezes precisamos apenas consultar os valores sem saber o índice da linha ou coluna, apenas tendo os valores correspondentes. Por exemplo: encontrar o salário de um funcionário com ID de funcionário. Nele, procuraremos o ID do funcionário para o índice de linha e Salário para o índice de coluna. Vamos aprender como podemos fazer essa pesquisa usando INDEX & MATCH.
Como resolver o problema?
Para que a fórmula seja entendida primeiro precisamos revisar um pouco sobre as seguintes funções
- Função INDEX
- Função MATCH
Agora faremos uma fórmula usando as funções acima. A função de correspondência retornará o índice do valor 1 de pesquisa no campo de cabeçalho da linha. E outra função MATCH retornará o índice do valor de pesquisa 2 do campo de cabeçalho da coluna. Os números de índice agora serão alimentados na função INDEX para obter os valores sob o valor de pesquisa dos dados da tabela.
Fórmula Genérica:
| = INDEX (dados, MATCH (lookup_value1, row_headers, 0, MATCH (lookup_value2, column_headers, 0))) |
dados : array de valores dentro da tabela sem cabeçalhos
lookup_value1 : valor a ser pesquisado no row_header.
row_headers : Array de índice de linha a ser pesquisado.
lookup_value1 : valor a ser pesquisado no column_header.
column_headers : coluna Matriz de índice a ser pesquisada.
Exemplo:
As afirmações acima podem ser complicadas de entender. Então, vamos entender isso usando a fórmula em um exemplo
Aqui, temos uma lista de pontuações obtidas pelos alunos com sua lista de assuntos. Precisamos encontrar a pontuação para um aluno (Gary) e sujeito (estudos sociais) específicos, conforme mostrado no instantâneo abaixo.
O Student value1 deve corresponder à matriz Row_header e Subject value2 deve corresponder à matriz Column_header.
Use a fórmula na célula J6:
| = INDEX (tabela, MATCH (J5, linha, 0, MATCH (J4, coluna, 0))) |
Explicação:
- A função MATCH corresponde ao valor do aluno na célula J4 com a matriz do cabeçalho da linha e retorna sua posição 3 como um número.
- A função MATCH corresponde ao valor do Assunto na célula J5 com a matriz do cabeçalho da coluna e retorna sua posição 4 como um número.
- A função INDEX obtém o número do índice da linha e da coluna, procura nos dados da tabela e retorna o valor correspondente.
- O argumento do tipo MATCH é fixado em 0. Como a fórmula irá extrair a correspondência exata.
Aqui, os valores da fórmula são fornecidos como referências de célula e row_header, table e column_header são fornecidos como intervalos nomeados.
Como você pode ver no instantâneo acima, obtivemos a pontuação obtida pelo aluno Gary no assunto Estudos Sociais como 36. E isso prova que a fórmula funciona bem e para dúvidas veja as notas abaixo para compreensão.
Agora usaremos a correspondência aproximada com cabeçalhos de linha e cabeçalhos de coluna como números. A correspondência aproximada leva apenas os valores numéricos, pois não há como se aplicar a valores de texto
Aqui temos um preço de valores de acordo com a Altura e Largura do produto. Precisamos encontrar o preço para uma altura (34) e largura (21) específicas, conforme mostrado no instantâneo abaixo.
O valor 1 da altura deve corresponder à matriz Row_header e o valor 2 da largura deve corresponder à matriz Cabeçalho da coluna.
Use a fórmula na célula K6:
| = ÍNDICE (dados, CORRESPONDÊNCIA (K4, Altura, 1, CORRESPONDÊNCIA (K5, Largura, 1))) |
Explicação:
- A função MATCH combina o valor da Altura na célula K4 com a matriz do cabeçalho da linha e retorna sua posição 3 como um número.
- A função MATCH combina o valor da largura na célula K5 com a matriz do cabeçalho da coluna e retorna sua posição 2 como um número.
- A função INDEX obtém o número do índice da linha e da coluna, procura nos dados da tabela e retorna o valor correspondente.
- O argumento do tipo MATCH é fixado em 1. Como a fórmula irá extrair a correspondência aproximada.
Aqui, os valores da fórmula são fornecidos como referências de célula e row_header, data e column_header são fornecidos como intervalos nomeados, conforme mencionado no instantâneo acima.
Você também pode realizar pesquisas de correspondências exatas usando as funções INDEX e MATCH no Excel. Saiba mais sobre como fazer pesquisa sensível a maiúsculas e minúsculas usando a função INDEX & MATCH no Excel. Você também pode procurar as correspondências parciais usando os curingas no Excel.
Como você pode ver no instantâneo acima, temos o Preço obtido por altura (34) & Largura (21) como 53,10. E isso prova que a fórmula funciona bem e para dúvidas veja as notas abaixo para mais compreensão.
Notas:
- A função retorna o erro #NA se o argumento da matriz de pesquisa para a função MATCH for uma matriz 2D, que é o campo de cabeçalho dos dados …
- A função corresponde ao valor exato, pois o argumento do tipo de correspondência para a função MATCH é 0.
- Os valores de pesquisa podem ser fornecidos como referência de célula ou diretamente usando o símbolo de aspas (") na fórmula como argumentos.
Espero que este artigo sobre como usar as funções ÍNDICE e CORRESPONDÊNCIA para pesquisar valor no Excel seja explicativo. Encontre mais artigos sobre como calcular valores e fórmulas Excel relacionadas aqui. Se você gostou de nossos blogs, compartilhe com seus amigos no Facebook. E você também pode nos seguir no Twitter e no Facebook. Gostaríamos muito de ouvir de você, diga-nos como podemos melhorar, complementar ou inovar nosso trabalho e torná-lo melhor para você. Escreva para nós no site de e-mail.
Use INDEX e MATCH para pesquisar o valor : A fórmula INDEX-MATCH é usada para pesquisar de forma dinâmica e precisa um valor em uma determinada tabela. Esta é uma alternativa para a função VLOOKUP e supera as deficiências da função VLOOKUP.
Use PROCV de duas ou mais tabelas de pesquisa : Para pesquisar em várias tabelas, podemos usar uma abordagem IFERROR. Para pesquisar em várias tabelas, ele considera o erro como uma opção para a próxima tabela. Outro método pode ser uma abordagem If.
Como fazer pesquisa sensível a maiúsculas e minúsculas no Excel : A função VLOOKUP do Excel não diferencia maiúsculas de minúsculas e retornará o primeiro valor correspondente da lista. INDEX-MATCH não é exceção, mas pode ser modificado para diferenciar maiúsculas de minúsculas. Vamos ver como …
Pesquisar texto que aparece com frequência com critérios no Excel : A pesquisa aparece com mais frequência em texto em um intervalo em que usamos INDEX-MATCH com a função MODE. Aqui está o método.
Artigos populares :
Como usar a função IF no Excel : A instrução IF no Excel verifica a condição e retorna um valor específico se a condição for TRUE ou retorna outro valor específico se FALSE.
Como usar a função PROCV no Excel : Esta é uma das funções mais usadas e populares do Excel, usada para pesquisar valores em diferentes intervalos e planilhas.
Como usar a função SUMIF no Excel : Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.
Como usar a função CONT.SE no Excel : Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar valores específicos. A função Countif é essencial para preparar seu painel.