How to find where in the interval a number is - For a particular category

In this thread we found a clever way to find where in an interval a number is.

Now consider a more complex case, where we have multiple intervals (e.g an interval for each drug) and you want to find “where in the interval of the matching drug” your number is.

See the image below for an illustration, and the attached file with the example: Find in interval - complex example.ods

I am thinking about using a SUMPRODUCT to automate the “calculated A Score” (and have similar columns for “caculated B score” and so on), but am struggling to find a way to integrate the previous finding into this example.

Note that the “score table” is something that I have control with, so I can change it to a different format to accommodate the solution.

Hello @migueldealmeida

Try insert formula =IF(I3<>"Yes";"Not taking class A";IFERROR(SUMPRODUCT(VLOOKUP(K3;IF(($B$3:$B$9=J3)*($A$3:$A$9="A");$C$3:$D$9);2;1));0)) in M3 cell. Please note, that Dosage values for each class/drug must be sorted ascending, otherwise you will get unexpected results. Also please note, that formula matches only A class rows and it is hardcoded in $A$3:$A$9="A" part.