On the assumption that
a) you only posted test data
b) there are cases where a colD value would "overlap" with 3 or more of the min / xam ranges,
then you'll need vba to stop this getting very messy.
If the most overlaps you'll get is 2 you could do this managably with formulas.
...more info please.