VLOOKUP equation in VBA
 

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

VLOOKUP equation in VBA
 

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

--
Regards,
Tom Ogilvy


"Ed" wrote:

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

VLOOKUP equation in VBA
 

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)
ResumeHe
Exit Function
ErrorHandler:
ComplexVLookup = CVErr(xlErrNA)
Resume ResumeHere
End Function

In article ,
Ed wrote:

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))/VLO
OKUP(L11,'Sheet'!$I$2:$K$19,3)*(VLOOKUP(VLOOKUP(L1 1,'Sheet'!$I$2:$K$19,1)+VLOO
KUP(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