Neste artigo, aprenderemos como pesquisar valores na tabela 2d usando uma função INDEX-MATCH-MATCH no Excel.
Cenário:
Suponha que você precise fazer várias pesquisas em uma tabela com centenas de colunas. Nesses casos, usar fórmulas diferentes para cada pesquisa demorará muito. Que tal criar uma fórmula de pesquisa dinâmica que você pode pesquisar pelo cabeçalho fornecido. Sim, podemos fazer isso. Essa fórmula é chamada de fórmula INDEX MATCH MATCH ou, digamos, uma fórmula de pesquisa 2d.
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
A função INDEX retorna o valor em um determinado índice em uma matriz.
A função MATCH retorna o índice da primeira aparição do valor em uma matriz (matriz de dimensão única).
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 2 de pesquisa no campo do 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 2D.
Fórmula Genérica:
= INDEX (dados, MATCH (lookup_value1, row_headers, 0, MATCH (lookup_value2, column_headers, 0)))
Dados: matriz 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.
Isso prova que a fórmula funciona bem e para dúvidas consulte 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.
Como você pode ver no instantâneo acima, temos o Preço obtido por altura (34) & Largura (21) como 53,10. Isso prova que a fórmula funciona bem e para dúvidas consulte 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 você tenha entendido como usar a tabela Lookup em 2 D usando a função INDEX & MATCH no Excel. Explore mais artigos sobre valor de pesquisa do Excel aqui. Sinta-se à vontade para expressar suas dúvidas abaixo na caixa de comentários. Certamente iremos ajudá-lo.
Use INDEX e MATCH para pesquisar o valor : Função INDEX & MATCH para pesquisar o valor conforme necessário.
Intervalo SUM com INDEX no Excel : Use a função INDEX para encontrar a SOMA dos valores conforme necessário.
Como usar a função SUM no Excel : Encontre a SOMA dos números usando a função SOMA explicada com o exemplo.
Como usar a função INDEX no Excel : Encontre o INDEX da matriz usando a função INDEX explicada com o exemplo.
Como usar a função COMBINAR no Excel : Encontre o MATCH na matriz usando o valor INDEX dentro da função MATCH explicada com o exemplo.
Como usar a função LOOKUP no Excel : Encontre o valor de pesquisa na matriz usando a função LOOKUP explicada com o exemplo.
Como usar a função PROCV no Excel : Encontre o valor de pesquisa na matriz usando a função VLOOKUP explicada com o exemplo.
Como usar a função HLOOKUP no Excel : Encontre o valor de pesquisa na matriz usando a função HLOOKUP explicada com o exemplo.
Artigos populares
50 Atalho do Excel para aumentar sua produtividade
Editar uma lista suspensa
Referência absoluta no Excel
Se com formatação condicional
Se com curingas
Vlookup por data
Junte-se ao nome e sobrenome no excel
