Excel: Formulas Personalizadas - xLookup
O Excel tem algumas de funções de pesquisa muito utilizadas, como é o caso do VLOOKUP(), HLOOKUP(), INDEX(), etc. Estas funções permitem pesquisar por um valor e, em alguns casos, retornar um resultado de uma coluna adjacente.
Por exemplo a função VLOOKUP() funciona da seguinte forma:
=VLOOKUP(«valor a procurar»;«área a procurar»;«coluna retorno»;«busca parcial»)
Como podem ver na imagem, a tabela de valores vai de A2 a B11. Se colocarmos o valor “1” em A14 (que será o valor a pesquisar) e a formula =VLOOKUP(A14;A2:B11;2;FALSE) na célula B14, teremos o seguinte resultado: “um”.
Isto é o funcionamento desta formula, que vai procurar um valor a uma área e retorna o valor de uma determinada coluna, da linha encontrada.
No entanto, podemos querer tornar esta função mais poderosa e com isso indicar vários valores e retornar vários resultados. Para isso podemos criar uma função personalizada (UDF - User Defined Function), que neste caso foi designada por xLookup().
Public Function xVLOOKUP(ByVal str As String) As String
Dim searchValues() As String
Dim rng As Range, searchID As Range
Dim x As Integer, columnIndex As Integer
Dim result As String
' Define o area de pesquisa
Set rng = [a2:b11]
' Define qual a coluna onde estão os resultados
columnIndex = 2
' Separa os ID's que se encontram separados por
' ponto e virgula ao array criado
searchValues = Split(str, ";")
' Ciclo nos ID's encontrados
For x = 0 To UBound(searchValues)
' Pesquisa o ID na area definida(limpando também espaços)
searchID = rng.Find(What:=Trim(searchValues(x)), SearchFormat:=False)
' Caso encontra colocar o resultado da linha encontrada
' e de acordo com a coluna, caso contrário um texto geral
If Not searchID Is Nothing Then
result = result & Cells(searchID.Row, columnIndex).Value & ";"
Else
result = result & "Não Encontrado;"
End If
Next
' Atribui o resultado à função, removendo o ";" final
If Len(result) > 1 Then
xVLOOKUP = Mid(result, 1, Len(result) - 1)
Else
xVLOOKUP = result
End If
End Function
Neste caso, o que a função faz, é separar o valor de uma célula para uma array e depois pesquisar valor a valor, construindo o resultado.
Como podem na imagem, se colocarmos “1;2;3;1” na célula A17, e a formula =xVLOOKUP(A17) em B17, obtemos o resultado “um;dois;três;um”
É mais um exemplo de uma UDF e de como podemos criar novas formulas, para além das inúmeras que já que temos disponíveis. Mostra também como fazer uma pesquisa numa área definida e de como utilizar os resultados dessa pesquisa.
NOTA: Estas são aplicações práticas colocadas em fóruns, newsgroups, blogs, etc, e como mostram algum código e poderem ser utilizadas em outros contextos, são aqui publicadas.