geting a if/and number off of two list cells

[nbach]

I need to figure out a formula (or a set of formulas) to get a number off of
a combination of two different drop down lists.

My two lists are like this:
A - Frequent
B - Probable
C - Occasional
D - Remote
E - Improbable

and:
I - Catastrophic
II - Critical
III - Marginal
IV - Negligible

I need to combine it so that each different combination gives you a
different risk number. Like A and I gives you 20, B and I = 19, A and IV =
12, B and IV = 8 and so on.

I know how to do the formula when it is two static cells
=IF(AND(B4="A - Frequent", C4="I - Catastrophic"), 20, 0)

And I have seen examples of how two use a VLOOKUP with a list, which seems
like what I need to use, but I don't know how to get it to work when you need
to combine two different lists to get one result.


Can anyone help me with this? I've seen all the different formulas that
seem to be what I need, but I'm at a loss to figure out how to put them
together.

Thanks!

 

[Mike H]

Hmm,

Risk assesments. If yours are done in this way then one way would be to
produce a list of all possible combinations and then assign the numeric value
to each.

Put your frequency list in Column A and your severity list in Column B both
starting in row 1. Right click your sheet tab, view code and paste this code
in and run it
There mustn't be any other data in columns A or B

Sub stance_abuse()
lastrowa = Cells(Rows.Count, "A").End(xlUp).Row
lastrowb = Cells(Rows.Count, "B").End(xlUp).Row
For x = 1 To lastrowa
For y = 1 To lastrowb
Cells(x + z, 3).Value = Cells(x, 1).Value & Cells(y, 2).Value
If y < lastrowb Then z = z + 1
Next
Next
End Sub

You will have a complete list of all possible combinations in column C.
Assign numeric values to each in column D and copy the table to somewhere
out of the way. A good idea would be to give it a name such as RiskTable

You can then do your lookup like this

=VLOOKUP(B4&C4,G1:H20,2,FALSE)

or if you name the range like this

=VLOOKUP(B4&C4,RiskTable,2,FALSE)

Mike

 

[Rick Rothstein]

I think there is something wrong with your description of how the points are
attributed to the combination of letters and Roman numerals.

You said AI=20 and BI=19...next higher letter for the same Roman numeral
decreased the value by one

However, you also said AIV=12 and BIV=8... next higher letter for the same
Roman numeral decreased the value by four.

Can you simply list the letter-Roman numeral combinations (there are only 20
of them) with the values you want assigned to them?

Also, are the letters and Roman numerals in separate columns or are they a
text string (like CIII for example)?