Help with list of values

[Abbigail]

Hi,



I would appreciate your advice; I have a spreadsheet in Excel, which I'm
trying to make as easy as possible for users (seriously).



I have three columns and in two of the columns I have a drop down list, how
can I get the third column to come up with the answer, however columns one
and two have many different combinations. The answer is also in text and
not in numbers, examples could be:



Negligible + Moderate = Medium

(Column 1, list of values + Column 2, list of values = Column 3 - this value
would just appear).



There are 5 values in each of the list of values in columns 1 and 2, and
there combination can equal a total of any 4 in column 3.



I hope the above makes sense, and any help would be appreciated.



Thanks,

 

[somethinglikeant]

Hi Abbigail,

You can use an IF function containing AND and OR operators to do what
you need.

If you have 5 choices in column 1, 5 choices in column 2. then there
are 25 possible combinations. And you mentioned this needs to result in
the result of a possible 4 values.

You need to work out what your rule is to map each of these 25
combinations to a result and then figure out what your function needs
to be

Eg

Column 1 Column 2 Column 3
Cake Morning Bad*
Kebab Evening Bad*

The function in column 3 * might be
IF(OR(AND(A2="Cake",B2="Morning"),AND(A2="Kebab",B2="Evening")),"Bad","Good")

Of coure since you have 25 coimbinations tis formula may be quite
lengthy

just draw out your combinations and try and work out your rule.

Ant

 

[Bob Phillips]

I would create a table of all combinations with their associated values and
so a lookup on that.

For instance, in the table have

NegligibleModerate Medium
NegligibleSevere High

and use a lookup like

=VLOOKUP(A1&B1,M1:N20,2,False)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)