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.