Evaluate on multiple columns and return a value

[CynthiaF]

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 are:

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!

 

[Spiky]

If it were me, I'd make a little table elsewhere and use it as a
lookup. 2 columns:

Column AA:
HighYesHigh
HighYesMed
HighYesLow
MedYesHigh

....and so forth for all 18. No spaces.

Column AB would have the appropriate values for each choice.

Column D would have a simple formula:
=VLOOKUP(A1&B1&C1,$AA$1:$AB$18,2,FALSE)

 

[TomPl]

Build a table with you 18 possible answers.

e.g. HighNoLOw One Possible Result
HighYesLow Another Result

Then, in column D lookup the result you want with a formula like this:

=VLOOKUP(A7&B7&C7,H4:I5,2,FALSE)

I hope that makes sense.

Tom

 

[Pete_UK]

Suppose you build up a table with 18 rows and 2 columns like this:

HighYesHigh 400
HighYesMed other numbers to suit choices
HighYesLow x
HighNoHigh x
HighNoMed x
HighNoLow x
MedYesHigh x
MedYesMed x
MedYesLow x
MedNoHigh x
MedNoMed x
MedNoLow x
LowYesHigh x
LowYesMed x
LowYesLow x
LowNoHigh x
LowNoMed x
LowNoLow x

and suppose this occupies M1:N18. Then in D1 you could have this
formula:

=VLOOKUP(A1&B1&C1,M$1:N$18,2,0)

which will return the appropriate number based on your choices in A1,
B1 and C1.

Hope this helps.

Pete

 

[ShaneDevenshire]

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.

 

[CynthiaF]

Thanks for the suggestions! I got the VLOOKUP to work. I couldn't get the
LOOKUP option to work, but no doubt that was user error.

Thanks!