Hi,

Here are two other slighly different approaches

=LOOKUP(A1&B1&C1,H1:H18&I1:I18&J1:J18,L1:L18)

In this example the three drop downs are in A1:C1 and the possibilities for
each are entered in separate columns H1:H18, I1:I18 and J1:J18 and the values
you assign are in L1:L18. This approach is nice because you can keep the
three answers in three columns. Notice that VLOOKUP can't handle the
concatented lookup table but LOOKUP can.

or

=LOOKUP(A1&B1&C1,K1:K18,L1:L18)

In this case K1:K18 contains the concatenated possible answers such as
HighYesHigh and L1:L18 contains the associated values. The advantage is a
shorter formula.


If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"CynthiaF" wrote:

I have a graphical decision tree that I use to estimate work effort based on
three decision points. The combination of points and responses results in 18
possible answers (work effort estimates). I'm trying to find a way to
automate this in an Excel spreadsheet of projects.

What I currently have are the three decision points as three drop-downs
based on lists I've set up on a separate worksheet. The options a

Column A = Possible answers are High, Med, and Low
Column B = Possible answers are Yes, No
Column C = Possible answers are High, Med, Low

What I'd like to do is evaluate the answers selected in A, B, and C and
populate a Column D with the value provided on the decision tree. (Note that
at this point I don't have those 18 values stored anywhere in the XLS.)

Note that any one selection does not impact the other selections - they are
mutually exclusive. Hence the possible 18 combinations/results.

The only way that I know to do this is with an IF statement (i.e. IF A=High
and B=Yes and C=High then populate D with 400) where I would have to provide
all of the decision tree values for each combo. But I'm not particularly keen
on writing an 18 argument IF statement.

I'm guessing there is some more efficient way to do this, but I have no idea
what it might be. I've looked at some of the other functions and can't see
how they would achieve what I'm looking for.

Thanks in advance for assistance!
--
Cynthia