Tentando copiar a função do vlookup no VBA, o melhor que consigo fazer é o seguinte:
Function VBALOOKUP(LUcell As Variant, LUArray As Range, LUColi As Long, OutColi As Long) As Variant
Dim Y() As Variant
Dim X As Long
'cycle through each cell in range and check if = to lookup cell then return value with same index in outcol
Y = LUArray
For X = LBound(Y) To UBound(Y)
If LUcell = Y(X, LUColi) Then
VBALOOKUP = Y(X, OutColi)
Exit For
End If
Next X
Erase Y
End Function
Funciona, mas é incrivelmente lento (cerca de 100x mais lento que a função de planilha).
Parece que talvez uma maneira de acelerá-lo seria classificar a matriz de pesquisa em ordem alfabética e, de alguma forma, pular para um ponto posterior na lista. Não tenho experiência aqui, mas parece que olhar para cada célula individual para verificar se é igual ao valor de pesquisa, antes de passar para a próxima célula, simplesmente não pode ser a maneira mais eficiente de fazer isso e não pode ser como funciona a função vlookup real.
Se você está se perguntando "por que não usar apenas o vlookup", estou escrevendo um script em VBA que em determinado momento executa algo semelhante a um vlookup (embora a situação específica seja um pouco mais complicada e retorne vários valores, não apenas 1) . Em vez de pedir ao stackexchange para escrever todo o meu código para mim, pensei em chegar ao cerne da questão. Além disso, acredito que esta pergunta será útil para referência futura para muito mais pessoas do que seria uma pergunta sobre minha tarefa específica.