After days of running into Excel limitations I have resorted to trying to do
this in VB (definitely not my strength). I have too many nested conditionals
for Excel to handle so I wanted to create a function in VB. I need to
convert VLOOKUP(D50,INDIRECT(D50&"_Contract"),2,FALSE) to the VB equivalent.
DLookup looks to be the best I could find, but one clear problem I forsee is
DLookup is using text and my commision table is full of numbers. Most of the
table on a different tab than the computations looks like this:

Life LTC $MART H.O.M.E. G.O.O.D. M Funds
REP 25% 10.00% 0.31% 0.31% 0.31% 30.00%
SREP 35% 15.50% 0.36% 0.36% 0.36% 32.50%
DIS 50% 20.00% 0.44% 0.44% 0.44% 35.00%
DIV 60% 25.00% 0.57% 0.57% 0.57% 37.50%
REG 70% 30.00% 0.83% 0.83% 0.83% 42.50%
SREG 80% 35.00% 0.83% 0.83% 0.83% 47.50%
RVP 95% 40.00% 1.23% 1.23% 1.25% 62.00%


D51 is SREG. So, in E51 I am looking up what the Life Product compensation
for a SREG when the customer pays an E19 premium. If E19, Life_Prem, was
$100, then
E51=VLOOKUP(D51,INDIRECT(D51&"_Contract"),2,FALSE) *(Life_Prem*12)*75%)*82.226%),
where SREG_Contract is a named range from the above SREG through to 47.50%
horizontally. SREG's commission is 80%; E51 should return 80%*100*12*75%=720

Your help is VERY appreciated!

Rod,

Have you looked at using the SUMPRODUCT function? With it you can use many
conditions. You would use it like this:

-SUMPRODUCT(--(Contract="SREG"),--(Second
Condition="Whatever"),--(Third="More"), Life_Premium)

As long as all of your conditions limit it to one answer you will get the
one result you want. If it results in multiple answers then it would sum
them together. I'm not sure if this work for you but you might look into it
and see if it will.

Mike

"Rod" wrote:

After days of running into Excel limitations I have resorted to trying to do
this in VB (definitely not my strength). I have too many nested conditionals
for Excel to handle so I wanted to create a function in VB. I need to
convert VLOOKUP(D50,INDIRECT(D50&"_Contract"),2,FALSE) to the VB equivalent.
DLookup looks to be the best I could find, but one clear problem I forsee is
DLookup is using text and my commision table is full of numbers. Most of the
table on a different tab than the computations looks like this:

Life LTC $MART H.O.M.E. G.O.O.D. M Funds
REP 25% 10.00% 0.31% 0.31% 0.31% 30.00%
SREP 35% 15.50% 0.36% 0.36% 0.36% 32.50%
DIS 50% 20.00% 0.44% 0.44% 0.44% 35.00%
DIV 60% 25.00% 0.57% 0.57% 0.57% 37.50%
REG 70% 30.00% 0.83% 0.83% 0.83% 42.50%
SREG 80% 35.00% 0.83% 0.83% 0.83% 47.50%
RVP 95% 40.00% 1.23% 1.23% 1.25% 62.00%


D51 is SREG. So, in E51 I am looking up what the Life Product compensation
for a SREG when the customer pays an E19 premium. If E19, Life_Prem, was
$100, then
E51=VLOOKUP(D51,INDIRECT(D51&"_Contract"),2,FALSE) *(Life_Prem*12)*75%)*82.226%),
where SREG_Contract is a named range from the above SREG through to 47.50%
horizontally. SREG's commission is 80%; E51 should return 80%*100*12*75%=720

Your help is VERY appreciated!