VLOOKUP equation in VBA

[Guest]

Hi all,

Looking for help creating an equivalent VBA public function statement for
the following VLOOKUP formula:

=VLOOKUP(L11,'Sheet'!$I$2:$K$19,2)+(L11-VLOOKUP(L11,'Sheet'!$I$2:$K$19,1))/VLOOKUP(L11,'Sheet'!$I$2:$K$19,3)*(VLOOKUP(VLOOKUP(L11,'Sheet'!$I$2:$K$19,1)+VLOOKUP(L11,'Sheet'!$I$2:$K$19,3),'Sheet'!$I$2:$K$19,2)-VLOOKUP(L11,'Sheet'!$I$2:$K$19,2)

Thanks in advance for everyone's help,

Ed

 

[Guest]

That specific formula

Public function MySpecialLookup()
s = "VLOOKUP(L11,'Sheet'!$I$2:$K$19,2)+"
s1="(L11-VLOOKUP(L11,'Sheet'!"$I$2:$K$19,1))/"
s2="VLOOKUP(L11,'Sheet'!$I$2:$K$19,3)*(VLOOKUP("
s3="VLOOKUP(L11,'Sheet'!$I$2:$K$19,1)+VLOOKUP("
s4="L11,'Sheet'!$I$2:$K$19,3),'Sheet'!$I$2:$K$19,2)"
s5 = "-VLOOKUP(L11,'Sheet'!$I$2:$K$19,2)"
MySpecialLookup = Evaluate(s & s1 & s2 & s3 & s4 & s5)
End Function

 

[JE McGimpsey]

You're missing a closing parenthesis somewhere. I assume it's at the
end, since otherwise the first term and last term would cancel out.
Assuming you want the function, e.g., to call

=VLOOKUP(L11,rng,2) + (L11 - VLOOKUP(L11,rng,1)) /
VLOOKUP(L11,rng,3) * (VLOOKUP(VLOOKUP(L11,rng,1) +
VLOOKUP(L11,rng,3),rng,2) - VLOOKUP(L11,rng,2))

as

=ComplexVLookup(L11, Sheet!I2:K19)


This could be replaced by


Public Function ComplexVLookup( _
ByVal what As Variant, rng As Range) As Variant
Dim dOne As Double
Dim dTwo As Double
Dim dThree As Double
Dim dFour As Double
On Error GoTo ErrorHandler
With Application
dOne = .VLookup(what, rng, 1, True)
dTwo = .VLookup(what, rng, 2, True)
dThree = .VLookup(what, rng, 3, True)
dFour = .VLookup(dOne + dThree, rng, 2, True)
End With
ComplexVLookup = dTwo + (what - dOne) / dThree * (dFour - dTwo)
ResumeHere:
Exit Function
ErrorHandler:
ComplexVLookup = CVErr(xlErrNA)
Resume ResumeHere
End Function