Na função VLOOKUP, geralmente definimos col_index_no static. Nós o codificamos dentro da fórmula VLOOKUP, como VLOOKUP (id, dados,3, 0). O problema surge quando inserimos ou excluímos uma coluna nos dados. Se removermos ou adicionarmos uma coluna antes ou depois da 3ª coluna, a 3ª coluna não se referirá mais à coluna pretendida. Este é um problema. Outro é quando você tem várias colunas para pesquisar. Você precisará editar o índice da coluna em cada fórmula. Copiar e colar simples não ajudará.
Mas e se você puder dizer VLOOKUP para olhar os títulos para e retornar apenas o valor dos títulos correspondentes. Isso é chamado de VLOOKUP bidirecional.
Por exemplo, se eu tiver uma fórmula VLOOKUP para omarcas coluna, então VLOOKUP deve procurar marcas coluna em dados e valor de retorno dessa coluna. Isso resolverá nosso problema.
Hmm … Ok, então como vamos fazer isso? Usando a função Match na função VLOOKUP.
Fórmula Genérica
=PROCV(lookup_value, table_array, MATCH (lookup_heading, table_headings, 0), 0)
Valor_procurado: o valor de pesquisa na primeira coluna de table_array.
Table_array: o intervalo no qual você deseja fazer uma pesquisa. Por exemplo, A2, D10.
Lookup_heading: o título que você deseja consultar nos títulos de table_array.
Table_headings: Referência dos títulos na matriz da tabela. Por exemplo. se a tabela for A2, D10 e os títulos no topo de cada coluna, então é A1: D1.
Então, agora sabemos o que precisamos para o col_index dinâmico, vamos esclarecer tudo com um exemplo.
Exemplo dinâmico de VLOOKUP
Para este exemplo, temos esta tabela que contém dados de alunos no intervalo A4: E16.
Usando o número do rolo e o título, desejo recuperar os dados desta tabela. Para este exemplo, na célula H4, quero obter dados de roll não escritos na célula G4 e de cabeçalho em H3. Se eu mudar o título, os dados do respectivo intervalo devem ser recuperados na célula H4.
Escreva esta fórmula na célula H4
= PROCV (G4, B4: E16, CORRESPONDÊNCIA (H3, B3: E3,0), 0)
Como nossa matriz de tabela é B4: E16, nossa matriz de cabeçalhos se torna B3: E3.
Observação: Se seus dados estiverem bem estruturados, os títulos das colunas terão o mesmo número de colunas e serão a primeira linha da tabela.
Como funciona:
Portanto, a parte principal é avaliar o número do índice da coluna automaticamente. Para fazer isso, usamos a função MATCH.
CORRESPONDÊNCIA (H3, B3: E3,0): Como H3 contém “aluno”, MATCH retornará 2. Se H3 tivesse “nota”, teria retornado 4 e assim por diante. A fórmula VLOOKUP finalmente terá seu col_index_num.
= PROCV (G4, B4: E16,2,0)
Como sabemos, a função MATCH retorna o número do índice de um determinado valor no intervalo unidimensional fornecido. Portanto, MATCH pesquisará qualquer valor escrito em H3 no intervalo B3: E3 e retornará seu número de índice.
Agora, sempre que você alterar o título em H3, se for em títulos, esta fórmula retornará um valor da respectiva coluna. Caso contrário, você terá um erro # N / A.
PROCV em várias colunas rapidamente
No exemplo acima, precisávamos da resposta de um valor de coluna. Mas e se você quiser obter várias colunas de uma vez. Se você copiar a fórmula acima, ela retornará erros. Precisamos fazer algumas pequenas alterações para torná-lo portátil.
Usando referências absolutas com VLOOKUP
Escreva a fórmula abaixo na célula H2.
= PROCURA ($ G2, $ B $ 2: $ E $ 14, CORRESPONDÊNCIA (H $ 1, $ B $ 1: $ E $ 1,0), 0)
Agora copie H2 em todas as células no intervalo H2: J6 para preenchê-lo com dados.
Como funciona:
Aqui eu dei referência absoluta de cada intervalo, exceto a linha no valor de pesquisa para VLOOKUP ($ G2) e coluna em lookup_value para MATCH (H $ 1).
$ G2: Isso permitirá que a linha mude para o valor de pesquisa para a função VLOOKUP enquanto copia para baixo, mas restringe a coluna para mudar quando copiada para a direita. O que fará PROCV procurar pelo Id da coluna G apenas com a linha relativa.
De forma similar, H $ 1 permitirá que a coluna mude quando copiada horizontalmente e restringirá a linha quando copiada para baixo.
Usando intervalos nomeados
O exemplo acima funciona bem, mas torna-se difícil de ler e escrever esta fórmula. E isso não é nada portátil. Isso pode ser simplificado usando intervalos nomeados.
Faremos alguns nomes aqui primeiro. Para este exemplo, chamei
$ B $ 2: $ E $ 14: como dados
$ B $ 1: $ E $ 1: como títulos
H $ 1: Nomeie-o como Título. Torne as colunas relativas. Para fazer isso, selecione H1. Pressione CTRL + F3, clique em novo, na seção Refers to remove '$' da frente de H.
$ G2: Da mesma forma, nomeie-o como RollNo. Desta vez, torna a linha relativa removendo '$' da frente de 2.
Agora, quando você tiver todos os nomes na planilha, escreva esta fórmula em qualquer lugar do arquivo excel. Sempre obterá a resposta correta.
= VLOOKUP (RollNo, Data, MATCH (Heading, Headings, 0), 0)
Veja, qualquer um pode ler e entender isso.
Portanto, usando esses métodos, você pode tornar col_index_num dinâmico. Deixe-me saber se isso foi útil na seção de comentários abaixo.
Como usar tFunção VLOOKUP no Excel
Referência relativa e absoluta no Excel
Intervalos nomeados no Excel
Como PROCURAR em planilhas diferentes do Excel
VLOOKUP Valores Múltiplos
Artigos populares
50 Atalho do Excel para aumentar sua produtividade : Torne sua tarefa mais rápida. Esses 50 atalhos farão você trabalhar ainda mais rápido no Excel.
Como usar tFunção VLOOKUP 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 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.
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.