Multiplying two validation lists to get a ranking

[watermt]

I have two validation lists, each have four options in their respective list
with ranges of 4 to 1. I need a formula that will display the results of the
selection in the Probability column x Severity of Effect column and display
the results under the Ranking column.

Probability Severity of Effect Ranking
Frequent x Catastrophic 16
Occasional x Major 9
Uncommon x Moderate 4
Remote x Minor 2

 

[N harkawat]

=hlookup(a1,{"frequent","occa,,,","uncc...","remote";4,3,2,1},2,0) *
hlookup(b1,{"cat","maj","mod","minor";4,3,2,1},2,0)

where a1 and b1 are your validation lists

correct the spelling in the formula

 

[watermt]

Iuch appreciated there N harkawat - I'm still learning this Excel program
that i've only really used in the past as a flat file organizing type of
program. I learn something new every day and most, if not all, of it comes
from these discussion forums.

Thanks,
Mike