A fórmula Vlookup funciona apenas quando a matriz da tabela na fórmula não muda, mas se houver uma nova coluna inserida na tabela ou uma coluna for excluída, a fórmula dá um resultado incorreto ou reflete um erro, para tornar a fórmula livre de erros em Em tais situações dinâmicas, usamos a função match para realmente combinar o índice dos dados e retornar o resultado real.
Combine VLOOKUP com Match
A fórmula vlookup é a função mais comumente usada para pesquisar e retornar o mesmo valor no índice de coluna especificado ou o valor de um índice de coluna diferente com referência ao valor correspondente da primeira coluna. O principal desafio enfrentado ao usar o vlookup é que o índice da coluna a ser especificado é estático e não tem uma funcionalidade dinâmica. Especialmente quando você está trabalhando em vários critérios que exigem que você altere o índice da coluna de referência manualmente. Assim, essa necessidade é atendida usando a fórmula “MATCH” para ter uma melhor aderência ou controle ao índice de coluna que muda frequentemente na fórmula VLOOKUP.
VLookup and Match Formula
# 1 - Fórmula VLOOKUP
A fórmula da função VLOOKUP no Excel
Aqui, todos os argumentos a serem inseridos são obrigatórios.
- Valor_procurado - Aqui, a célula ou texto de referência com aspas duplas deve ser inserido para ser identificado no intervalo da coluna.
- Matriz da tabela - este argumento requer que o intervalo da tabela seja inserido onde o valor_procurado deve ser pesquisado e os dados a serem recuperados residem no intervalo da coluna particular.
- Col_index_num - Neste argumento, o número do índice da coluna ou a contagem da coluna da primeira coluna de referência precisa ser inserido, a partir da qual o valor correspondente precisa ser retirado da mesma posição que o valor pesquisado na primeira coluna.
- [Range_lookup] - Este argumento dará duas opções.
- VERDADEIRO - Correspondência aproximada: - O argumento pode ser inserido como VERDADEIRO ou numérico “1”, que retorna a correspondência aproximada correspondente à coluna de referência ou primeira coluna. Além disso, os valores na primeira coluna da matriz da tabela devem ser classificados em ordem crescente.
- FALSE - Correspondência exata: - Aqui, o argumento a ser inserido pode ser FALSE ou numérico “0”. Esta opção retornará apenas a correspondência exata do valor correspondente a ser identificado a partir da posição no primeiro intervalo da coluna. A falha em pesquisar o valor da primeira coluna retornaria uma mensagem de erro “# N / A”.
# 2 - Match Formula
A função Match retorna a posição da célula do valor inserido para a matriz de tabela fornecida.
Todos os argumentos dentro da sintaxe são obrigatórios.
- Valor_procurado - aqui, o argumento inserido pode ser a referência da célula do valor ou uma sequência de texto com aspas duplas cuja posição da célula deve ser puxada.
- Lookup_array - O intervalo da matriz da tabela deve ser inserido, cujo valor ou conteúdo da célula se deseja identificar.
- [tipo de correspondência] - Este argumento fornece três opções, conforme explicado abaixo.
- “1-Menor que” - Aqui o argumento a ser inserido é numérico “1” que retornará o valor que é menor ou igual ao valor de pesquisa. E também a matriz de pesquisa deve ser classificada em ordem crescente.
- “0-correspondência exata” - aqui, o argumento a ser inserido deve ser numérico “0”. Esta opção retornará a posição exata do valor de pesquisa correspondente. No entanto, a matriz de pesquisa pode estar em qualquer ordem.
- “-1-Maior que” - O argumento a ser inserido deve ser numérico “-1”. A terceira opção encontra o menor valor maior ou igual ao valor de pesquisa. Aqui, a ordem do array de pesquisa deve ser colocada em ordem decrescente.
# 3 - VLOOKUP com fórmula MATCH
= VLOOKUP (lookup_value, table_array, MATCH (lookup_value, lookup_array, [match_type]), [range lookup])
Como usar PROCV com fórmula de correspondência no Excel?
O exemplo a seguir ajudará a entender o funcionamento do vlookup e da fórmula de correspondência ao montar.
Você pode baixar este VLookup com Match Excel Template aqui - VLookup com Match Excel TemplateConsidere a tabela de dados abaixo que descreve as especificações do veículo a ser adquirido.
Para obter a clareza da função combinada para vlookup e função match, vamos entender como a fórmula individual opera e, em seguida, chegar aos resultados de correspondência vlookup quando colocados juntos.
Etapa # 1 - Vamos aplicar a fórmula vlookup em um nível individual para chegar ao resultado.
O resultado é mostrado abaixo:
Aqui, o valor de pesquisa é referido a $ B9 que é o modelo “E” e a matriz de pesquisa é fornecida como o intervalo da tabela de dados com valor absoluto “$”, o índice da coluna é referido à coluna “4” que é a contagem para a coluna “Tipo” e a pesquisa de intervalo recebem uma correspondência exata.
Assim, a seguinte fórmula é aplicada para retornar o valor da coluna “Combustível”.
O resultado é mostrado abaixo:
Aqui, o valor de pesquisa com a string absoluta “$” aplicada para o valor de pesquisa e lookup_array ajuda a fixar a célula de referência, mesmo se a fórmula estiver sendo copiada para uma célula diferente. Na coluna “Combustível”, precisamos alterar o índice da coluna para “5”, pois o valor a partir do qual os dados devem ser recuperados muda.
Etapa 2 - Agora, vamos aplicar a fórmula Match para recuperar a posição para o valor de pesquisa fornecido.
O resultado é mostrado abaixo:
Como pode ser visto na imagem acima, aqui estamos tentando recuperar a posição da coluna do array da tabela. Neste caso, o número da coluna a ser obtido é referido como célula C8 que é a coluna "Tipo" e o intervalo de pesquisa a ser pesquisado é fornecido como o intervalo de cabeçalhos de coluna e o tipo de correspondência recebe uma correspondência exata para ser " 0 ”.
Assim a tabela abaixo dará o resultado desejado para as posições da coluna “Combustível”.
Agora, aqui a coluna a ser pesquisada é dada como a célula D8 e o índice da coluna desejada é retornado como “5”.
Etapa # 3 - Agora, a fórmula Match será usada dentro da função vlookup para obter o valor da posição da coluna identificada.
O resultado é mostrado abaixo:
Na fórmula acima, a função de correspondência é colocada no lugar do parâmetro de índice da coluna da função vlookup. Aqui, a função de correspondência identificará a célula de referência do valor de pesquisa “C8” e retornará o número da coluna através da matriz de tabela fornecida. Esta posição da coluna servirá como entrada para o argumento do índice da coluna na função vlookup. O que, por sua vez, ajudará o vlookup a identificar o valor a ser retornado do número de índice da coluna resultante?
Da mesma forma, aplicamos vlookup com fórmula de correspondência para a coluna “Combustível” também.
O resultado é mostrado abaixo:
Podemos, portanto, aplicar esta função de combinação para outras colunas “Tipo” e “Combustível” também.
Coisas para lembrar
- VLOOKUP pode ser aplicado a valores de pesquisa apenas em seu lado esquerdo. Quaisquer valores presentes a serem pesquisados no lado direito da tabela de dados retornarão o valor de erro “# N / A”.
- O intervalo de table_array inserido no segundo argumento deve ser a referência de célula absoluta “$”, isso manterá o intervalo fixo da matriz da tabela ao aplicar a fórmula de pesquisa a outras células, ou então as células de referência para o intervalo da matriz da tabela irão mudar para a próxima célula referência.
- O valor inserido no valor de pesquisa não deve ser menor do que o menor valor na primeira coluna da matriz da tabela, caso contrário, a função retornará o valor de erro “# N / A”.
- Antes de aplicar uma correspondência aproximada “TRUE” ou “1” no último argumento, lembre-se sempre de classificar a matriz da tabela em ordem crescente.
- A função de correspondência retorna apenas a posição do valor na matriz da tabela vlookup e não retorna o valor.
- Caso a função Match não seja capaz de identificar a posição do valor de pesquisa na matriz da tabela, a fórmula retorna “# N / A” no valor de erro.
- As funções Vlookup e match não fazem distinção entre maiúsculas e minúsculas ao combinar o valor de pesquisa com o valor de texto correspondente na matriz da tabela.
