Compare cell value with multiple other variables to display a result

Hi,

I'm trying to create a calculator for use in my healthcare setting. I am stuck on the final part, which I want the calculator to spit out a pre-determined dose and directions. Here is how I have the 2 sheets set up.

p1.JPG

This sheet (calculator) contains the main calculator, the 'dose' field is where I need to put the formula.

p2.JPG

This sheet (data) contains the pre-determined dosages depending on whether we are treating (tx) or preventing (px) and based on the CrCl value.

I want the "Dose" cell (L4) to compare the CrCl cell range (J4) to the 'data' sheet and whether the order is to Tx or Px (K4) then pull the corresponding info from the 'data' sheet


I can't think of any way of doing this without resorting to a bunch of if then operators... any help would be much appreciated.

Hi,

I recommend changing your data array structure a little bit.
Then you can use a VLOOKUP function to pull your data.

Attached is a example using your data.

Hope this makes sense and is helpful.

Cheers

Thank you, there is no way I would have been able to solve this without your example.

Much appreciated!!!!

You are very welcome. Glad I could be of some assistance.

Cheers