in a single worksheet,hw to match values based on 2 list

[Max]

i got an excel sheet with 3 columns

the first column B9 is header "Risk Source classification"
the second column c9 has header "Risk effect"
the third column should be d9 with header "Risk description"


now from b10-d10 till b40-d40 we enter values as the user chooses like

B10 will have list values for "Risk source classification" predefined list
with about 80 values to choose from in the dropdown
C10 will have list values for "Risk effect" and has 3 values
"high","medium","low"

i.e when "Risk Source classification" and "Risk effect" are selected "Risk
description" should be populated automatically ie in d10.

Now in my excel rows say 4 to 40 are having rows for these values as
mentioned above.



and from row 51 they have specified again headers

D51 has header called "Risk Source classification" and D52,D53,D54 is
specified as headers with "high","medium","low"

for ex if d52 is "Risk source classification" value "workflow"
correspondingly
then for e52 "high" it has value "directly supports customer organization
mission and/or goals"
for f52 "medium"it has value "directly supports customer organization "
for g52 "low" it has value "to be null"

similarly values are defined for each risk source classification from
d52,e52,f52,g52 until d138,e138,f138,g138

now in B10 if user selects "Workflow" and if he chooses c10 as "high" then i
should automatically get value from e52 as "directly supports customer
organization mission and/or goals"


This way risk source classificaiton values range from D51 to D138 adn for
each there are 3 probable values for high, medium and low.
and the formula should be performed in d10 for any value selected.. how can
i achieve this please let me know.

hope i have asked the question in a way that can be understood

 

[Bernie Deitrick]

Max,

=INDEX($E$51:$G$138,MATCH(B10,$D$51:$D$138,FALSE),MATCH(C10,{"High","Medium","Low"},FALSE))

HTH,
Bernie
MS Excel MVP

 

[Max]

Thanks a lot Bernie
It worked.

Max,

=INDEX($E$51:$G$138,MATCH(B10,$D$51:$D$138,FALSE),MATCH(C10,{"High","Medium","Low"},FALSE))

HTH,
Bernie
MS Excel MVP