Identify sub-Range within Range depending on Input cell using VLOOKUP, MATCH, INDEX, ADDRESS

[rocketscientist165]

I have problems with the VLOOKUP, MATCH, INDEX and ADDRESS commands. I was hoping I could use these instead of a macro.

This is what I want to do:

I have 5 criteria with weights and assigned points. With this I get a result for the given alternative (sumproduct). Now my task is to show what would happen if I changed the weights of the criteria. So I have a column (B) with values from 0% to 50% in columns D,E,F,G and H I have the updated end-result for the new weight. To graph my sensitivity analysis (similar to box plot graph) I need the MIN and MAX values of the range from the original weight +/- 10%.

Let's say the weight of "crit_1" is 27% and the end-result would be 59 points. The Cell with 27% in column B is in Cell "B37", the result in "D37". Now I need the MIN and MAX of Range ("D27:D47"). Manually this works fine. But I can't figure out how to automatically update the range in the MIN/MAX formula if the weight of "crit_1" changes to 5% or other values.

Any ideas?

 

[rocketscientist165]

forgot to mention that the data with the weights and points are given at the top of the worksheet.
and as I said I tried using VLOOKUP to find the weight from the data table in column B and then give me the point value of column D. This works, but doesn't help me with my problem.