Como recuperar um valor de uma grade de referência de acordo com os valores do índice Salvar no Excel

Anonim

Neste artigo, aprenderemos como recuperar um valor de uma grade de referência de acordo com os valores de índice Salvar no Excel

Cenário:

Muitas vezes, ao trabalhar com dados longos não espalhados, precisamos primeiro limpar os dados antes de trabalhar neles. Leva tempo e você só queria extrair algumas consultas. Para extrair dados, geralmente você usa a função VLOOKUP. Mas quando temos dados longos com muitos campos de coluna, então fica confuso porque a função VLOOKUP requer o índice de coluna correto como número, ou então a fórmula retorna um erro. A seguir, está a explicação da fórmula para esse tipo de problema.

Como resolver o problema?

Para que a fórmula seja entendida primeiro precisamos revisar um pouco sobre as seguintes funções

  1. Função INDEX
  2. 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 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.

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 :

Tudo isso pode ser confuso de entender. Vamos entender como usar a função usando um exemplo. Aqui, temos alguns dados de pedido de amostra e precisamos encontrar os dados ausentes relacionados ao valor fornecido na tabela.

Aqui, precisamos encontrar os detalhes do pedido, como quantidade, região e preço para o pedido feito em 13-01-2019.

Use a fórmula:

= ÍNDICE (A2: D11, CORRESPONDÊNCIA (G3, A2: A11, 0), CORRESPONDÊNCIA (F4, A1: D1, 0))

Explicação:

  1. A função MATCH retorna o índice do valor combinado na matriz 1D fornecida (horizontal ou vertical).
  2. MATCH (F4, A1: D1,0) retorna 3 como índice.
  3. MATCH (G3, A2: A11,0) retorna 5 como Índice
  4. A função INDEX retornará o 5º elemento (começando da 2ª linha) na 3ª coluna.

Como você pode ver, a quantidade relacionada ao pedido feito em 13-01-2019 chega a ser 38. Agora podemos encontrar a região de onde o pedido foi feito usando a fórmula.

Use a fórmula:

= ÍNDICE (A2: D11, CORRESPONDÊNCIA (G3, A2: A11, 0), CORRESPONDÊNCIA (F5, A1: D1, 0))

Como você pode ver, o pedido foi feito na região sul. Agora pegue o preço relacionado ao pedido feito

Use a fórmula:

= ÍNDICE (A2: D11, CORRESPONDÊNCIA (G3, A2: A11, 0), CORRESPONDÊNCIA (F6, A1: D1, 0))

Como você pode ver, o preço acaba sendo 82,84. Você percebeu que extraímos todos os detalhes usando uma fórmula. A combinação INDEX e MATCH é melhor para encontrar um valor da tabela.

Aqui estão todas as notas observacionais sobre o uso da fórmula.

Notas:

  1. A fórmula funciona para texto e números.
  2. A função retorna o erro #NA se o argumento da matriz de pesquisa para a função MATCH não tiver o mesmo comprimento da matriz da tabela.
  3. A fórmula retorna um erro se lookup_value não corresponder ao valor na tabela lookup_array.
  4. A função corresponde ao valor exato, pois o argumento do tipo de correspondência para a função MATCH é 0.
  5. Use o argumento -1 para menor que, 0 para correspondência exata e 1 para maior que a correspondência de pesquisa.
  6. 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 recuperar um valor de uma grade de referência de acordo com os valores do índice Salvar no Excel. Explore mais artigos sobre valor de pesquisa do Excel e funções do Excel 2019 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 : 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 INDEX no Excel : Encontre o INDEX de uma matriz usando a função INDEX explicada com um 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.

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.