A função XLOOKUP é exclusiva para o programa interno do office 365. A função LOOKUP tem muitas funcionalidades que superam muitos dos pontos fracos das funções VLOOKUP e HLOOKUP, mas infelizmente não está disponível para nós agora. Mas não se preocupe, podemos criar uma função XLOOKUP que funcione exatamente da mesma forma que a próxima função XLOOKUP do MS Excel. Vamos adicionar funcionalidades a ele uma por uma.
Código VBA da função XLOOKUP
A função de pesquisa UDF abaixo resolverá muitos problemas. Copie ou baixe o suplemento xl abaixo do arquivo abaixo.
Função XLOOKUP (lk As Variant, lCol As Range, rCol As Range) XLOOKUP = WorksheetFunction.Index (rCol, WorksheetFunction.Match (lk, lCol, 0)) End Function
Explicação:
O código acima é apenas o INDEX-MATCH básico usado no VBA. Isso simplifica muitas coisas que um novo usuário enfrenta. If resolve a complexidade da função INDEX-MATCH e usa apenas três argumentos. Você pode copiá-lo em seu arquivo do Excel ou baixar o arquivo .xlam abaixo e instalá-lo como um suplemento do Excel. Se você não sabe como criar e usar um add-in, clique aqui, ele te ajudará.
Suplemento XLOOKUP
vamos ver como funciona na planilha do excel.
Sintaxe de XLOOKUP
| = XLOOKUP (lookup_value, lookup_array, result_array) |
lookup_value: Este é o valor que você deseja pesquisar no lookup_array.
lookup_array: É um intervalo unidimensional em que você deseja pesquisar o lookup_value.
result_array: É também um intervalo unidimensional. Este é o intervalo do qual você deseja recuperar o valor.
Vamos ver esta função XLOOKUP em ação.
Exemplos de XLOOKUP:
Aqui, tenho uma tabela de dados em excel. Vamos explorar algumas funcionalidades usando esta tabela de dados.
Funcionalidade 1. Exato Pesquisa no lado esquerdo e direito do valor de pesquisa.
Como sabemos, a função VLOOKUP do Excel não pode recuperar valores à esquerda do valor de pesquisa. Para isso, você deve usar a combinação complexa INDEX-MATCH. Mas não mais.
Supondo que precisamos recuperar todas as informações disponíveis na tabela de alguns números de rolo. Nesse caso, você também terá que recuperar a região, que está à esquerda da coluna do número do rolo.
Escreva esta fórmula, I2:
| = XLOOKUP (H2, $ B $ 2: $ B $ 14, $ A $ 2: $ A $ 14) |
Obtemos o resultado Norte para o rolo número 112. Copie ou arraste para baixo a fórmula nas células abaixo para preenchê-las com as respectivas regiões.
Como funciona?
O mecanismo é simples. Esta função procura o lookup_value no lookup_array e retorna o índice de uma primeira correspondência exata. Em seguida, usa esse índice para recuperar o valor de result_array. Esta função funciona perfeitamente com intervalos nomeados.
Da mesma forma, use esta fórmula para recuperar o valor de cada coluna.
Funcionalidade 2. Exata Horizontal Pesquisa acima e abaixo do valor de pesquisa.
O XLOOKUP também funciona como uma função HLOOKUP exata. A função HLOOKUP tem a mesma limitação que a função VLOOKUP. Ele não pode buscar o valor acima do valor de pesquisa. Mas XLOOKUP não funciona apenas como HLOOKUP, ele também supera essa fraqueza. Vamos ver como.
Hipoteticamente, se você quiser comparar dois registros. O registro de pesquisa que você já possui. O registro com o qual você deseja comparar está acima do intervalo_procurado. Use esta fórmula nesse caso.
| = XLOOKUP (H7, $ A $ 9: $ E $ 9, $ A $ 2: $ E $ 2) |
arraste a fórmula para baixo e você terá todo o registro de comparação de linhas.
Funcionalidade 3. Não há necessidade de número de coluna e correspondência exata padrão.
Ao usar a função PROCV, você deve informar o número da coluna da qual deseja buscar os valores. Para isso, é necessário contar as colunas ou usar alguns truques, contar com a ajuda de outras funções. Com este UDF XLOOKUP, você não precisa fazer isso.
Se você estiver usando PROCV apenas para buscar algum valor de uma coluna ou para verificar se existe um valor na coluna, esta é a melhor solução por mim.
Funcionalidade 4. Substitui a função INDEX-MATCH, VLOOKUP, HLOOKUP
Para tarefas simples, nossa função XLOOKUP substitui as funções mencionadas acima.
Limitações de XLOOKUP:
Quando se trata de fórmulas complexas, como VLOOKUP com índice de coluna dinâmica, em que VLOOKUP identifica a coluna de pesquisa com cabeçalhos, este XLOOKUP falhará.
Outra limitação é que se você tiver que procurar várias colunas ou linhas aleatórias da tabela, esta função será inútil, pois você terá que escrever esta fórmula repetidamente. Isso pode ser superado usando intervalos nomeados.
Por enquanto, não adicionamos a funcionalidade aproximada, então, é claro, você não pode obter a correspondência aproximada. Nós vamos adicionar isso em breve.
Se a função XLOOKUP falhar em encontrar o valor de pesquisa, ela retornará o erro #VALUE, não # N / A.
Então sim pessoal, é assim que você usa o XLOOKUP para recuperar, pesquisar e validar valores em tabelas do excel. Você pode usar esta função definida pelo usuário para uma pesquisa descomplicada à esquerda ou acima do valor de pesquisa. Se você ainda tiver alguma dúvida ou algum requisito específico relacionado a esta função ou consulta relacionada ao EXCEL 2010/2013/2016/2019/365 ou ao VBA, pergunte na seção de comentários abaixo. Você certamente receberá uma resposta.
Criar função VBA para retornar array | Para retornar um array de uma função definida pelo usuário, temos que declará-lo quando nomearmos a UDF.
Arrays no Excel Formul | Saiba o que são arrays no Excel.
Como criar uma função definida pelo usuário através do VBA. Aprenda a criar funções definidas pelo usuário no Excel
Usando uma função definida pelo usuário (UDF) de outra pasta de trabalho usando VBA no Microsoft Excel | Use a função definida pelo usuário em outra pasta de trabalho do Excel
Retornar valores de erro de funções definidas pelo usuário usando VBA no Microsoft Excel | Aprenda como você pode retornar valores de erro de uma função definida pelo usuário
Artigos populares:
Divida a planilha do Excel em vários arquivos com base na coluna usando o VBA | Este código VBA divide a planilha do Excel com base em valores exclusivos em uma coluna especificada. Baixe o arquivo de trabalho.
Desativar mensagens de aviso usando VBA no Microsoft Excel 2016 | Para desligar as mensagens de aviso que interrompem o código VBA em execução, usamos a classe Application.
Adicionar e salvar nova pasta de trabalho usando VBA no Microsoft Excel 2016 | Para adicionar e salvar pastas de trabalho usando o VBA, usamos a classe Workbooks. Workbooks.Add adiciona uma nova pasta de trabalho facilmente, no entanto …