Quem trabalha com PROCV sabe que ela é extremamente útil para fazer busca entre planilhas, mas sabe que ela é limitada a trazer o primeiro valor encontrado, varrendo a tabela de origem de cima para baixo. No post “Fazendo um “PROCV” invertido” eu já expliquei como fazer quando se quer buscar um registro varrendo a tabela de baixa para cima. Mas agora imagine uma tabela que existem 3 registros com a mesma chave. Com o PROCV você traz o primeiro valor, como o tutorial do PROCV INVERTIDO você traz o terceiro valor, então pergunto: “Como trazer um valor referente ao segundo nome encontrado na tabela?”
Neste primeiro tópico sobre UDF, quero mostrar uma forma de resolver este problema.
O significado da sigla UDF é “User defined Function”, ou seja, são funções personalizadas desenvolvidas pelos próprios usuários do Excel.
Normalmente recorre-se a uma UDF quando as funções disponíveis nativamente no Excel não são suficientes para atender a uma necessidade ou a necessidade é tão complexa que dependeria de muitas funções aninhadas ou muitos cálculos de apoio na planilha, ou para necessidades muito específicas que só podem ser resolvidas com programação.
Vamos desenvolver uma UDF que vou chamar de PROCVN, mas que poderia receber qualquer outro nome, conforme gosto do desenvolvedor. O código para a função ficará:
Public Function PROCVN(Val1 As Variant, _ Table As Range, _ ResultCol As Integer, _ Optional Val1Occrnce As Integer = 1) Dim i As Integer Dim iCount As Integer Dim rCol As Range For i = 1 To Table.Rows.Count If Not (Application.WorksheetFunction.IsError(Table.Cells(i, 1))) Then If UCase(Table.Cells(i, 1)) = UCase(Val1) Then iCount = iCount + 1 End If If iCount = Val1Occrnce Then PROCVN = Table.Cells(i, ResultCol) Exit For Else PROCVN = CVErr(xlErrNA) End If End If Next i End Function
Para tornar essa UDF disponível na sua planilha, pressione ALT+F11 para abrir o VBA, insira um módulo no seu projeto, e cole o código acima.
Feche o VBA e assim a UDF “PROCVN” já estará disponível para ser utilizada.
Falando da sintaxe da UDF, ficará:
=PROCVN(valor_procurado;Matriz_tabela;núm_índice_tabela;ordem_n-ésima)
A diferença do PROCV para esta UDF PROCVN está no 4º argumento que foi substituído pela ordem n-ésima, que é o argumento onde será indicado qual resultado de busca é desejado trazer de resposta, ou seja, é possível indicar qual resultado trazer (ex: 1 para primeiro, 2 para o segundo, 3 para o terceiro e assim por diante).
Note que nesta UDF não foi colocado a opção de VERDADEIRO ou FALSO, que seria referente ao tipo de busca, que quando indicado VERDADEIRO a função PROCV retorna o valor mais próximo encontrado. Como não estamos trabalhando com este argumento, a UDF só retornará a busca de valores exatos.
Dessa forma, para trazer o segundo resultado do nosso exemplo, a função ficará:
=PROCVN(F2;A2:B7;2;2)
É importante citar que para deixar a função disponível na sua planilha é preciso salvar o arquivo na extensão .xlsm (pasta de trabalho habilitada para macro do Excel. É possível também deixar a função disponível para qualquer pasta de trabalho se o código for aplicado por meio de um suplemento, assunto que tentarei abordar em algum futuro tópico.
Faça Download do modelo: Modelo – PROCVN
Tentei a sua explicação para um excel com 600 linhas mas ele não dá o resultado de todas, sabe como resolver?
CurtirCurtir
Olá Veronica!
Você poderia me encaminhar por e-mail mais detalhes da aplicação e se possível o arquivo ou modelo de como você está fazendo. Assim posso analisar melhor o motivo de não estar funcionando corretamente. Abs!
CurtirCurtir
Já lhe enviei email.
Cumprimentos.
CurtirCurtir
Alguém conseguiu aumentar i limite de linhas? minha plan tem mais de 40000 linhas, só consigo rodar pesquisando ate 32000
obrigada
CurtirCurtir
Estou enfrentando o mesmo problema da Verônica. Você poderia me informar qual foi a solução encontrada para o mesmo?
Abraço
CurtirCurtir
E como retornar apenas o último resultado encontrado?
CurtirCurtir
Acesse o artigo https://excelmaniacos.com/2015/07/09/funcoes-fazendo-um-procv-invertido-buscando-de-baixo-para-cima/
CurtirCurtir
Valeu…. me ajudou d+ esta formula… abraços e parabéns!!!!!
CurtirCurtir
Boa tarde, gostaria de fazer exatamente isso, mas sem a utilização de VBA ou macros. É possível?
CurtirCurtir
Olá Artur! É possível fazer com o uso de técnicas de fórmula matricial. No exemplo acima, uma possibilidade seria: =ÍNDICE(B2:B7;MAIOR(SE(A2:A7=F2;LIN(A2:A7)-1;””);2))
Sendo que o que vai determinar a ordem de busca é o segundo argumento da função MAIOR, que na fórmula acima é 2, ou seja, vai trazer o segundo valor encontrado para o nome Rafael. Importante ressaltar que essa fórmula só vai funcionar se inserida com CTRL+SHIFT+ENTER.
Abraços!
CurtirCurtir
Olá, Rafael, obrigado por compartilhar conhecimento. Estou respondendo a este comentário, pois a solução que eu procuro é próxima ao seu exemplo matricial, com um único detlhate (não solucionado até o momento por mim). O detalhe é fazer a fórmula matricial mostrar apenas os dados sem repetição (buscando de baixo para cima).
CurtirCurtir
Bom dia! Quando utilizo a UDF a fórmula me retorna #VALOR!. Sempre. Pode me ajudar?
CurtirCurtir
Estou com o mesmo problema, é um limitador de linhas da matriz que ele consulta, se vc diminuir verá que funciona.
Preciso aumentar esse limitador da matriz, se alguém souber como fazer.
Obrigado
CurtirCurtir
OLÁ!
tenho uma planilha com muitas linhas e algumas delas com dados iguais, segue um exemplo:
nome tipo data entrada data saída
1151 md#1 01/01/2000 01/10/2009
1152 md#1 04/08/2002
1151 md#2 06/07/2008
1153 md#1 06/07/1999
1151 md#1 05/10/2009
Gostei muito desse código, mas gostaria de saber se existe uma variação dele que funcione da seguinte forma
tenho a primeira variável de procura (val1) na coluna NOME encontrando-o faço uma segunda procura na coluna TIPO, fazendo assim uma distinção entre as linhas com nome igual, porque se o valor na segunda coluna não for a variável pesquisada, daí ele pula pra próxima e continua a pesquisa, até achar coluna1 e 2 correspondentes à procura. e aí retornar o valor da coluna DATA ENTRADA.
COMO UM PROV DE 2 CRITÉRIOS OU ALGO DO TIPO.
CurtirCurtir
Gostaria de obter resultados aproximados, como fazer?
CurtirCurtir
Inseri seu código em minha planilha mas a mesma não retorna todos os valores, pulando alguns itens de maneira randômica. Podes me indicar como solucionar tal problema?
CurtirCurtir
Muito Obrigado Funcionou aqui!
Parabens pela criação do código VBA.
CurtirCurtir
Eu utilizei a fórmula PROCVN mais algumas outras para retornar o que eu precisava.
Pois precisava que retornassem todos resultados na mesma célula.
=SEERRO(CONCATENAR(PROCVn(C15;$C$2:$D$10;2;1);”,”;PROCVn(C15;$C$2:$D$10;2;2);”,”;PROCVn(C15;$C$2:$D$10;2;3));SEERRO(CONCATENAR(PROCVn(C15;$C$2:$D$10;2;1);”,”;PROCVn(C15;$C$2:$D$10;2;2));PROCV(C15;$C$2:$D$10;2;0)))
Este exemplo é para até 3 resultados.
Ainda não testei em uma base de dados grande, mas qualquer ajuste é bem-vindo.
CurtirCurtir