looking up the values in two cells to retrieve the data that applies to both.

Sorry the title doesn't explain what I am trying to do very well... I want to be able to choose a drug type in one column and the route of administration in the next column and then have the dose information for that drug given in that route displayed across the next few cells in the same row. I have attached a simplified demo spreadsheet.

Originally I was using a vlookup command and had both the drug and the route in the same cell, however, I need the route in a separate cell for other use on the spreadsheet.

Also, I was hoping that by using this method I would be able to avoid a mile long drop down menu due to each drug being listed 3 - 5 times (for each route). However, the way I have it set up now the list is just as long, there are just blank spaces. Is there any way to get rid of the spaces in the drop down menu?

Thanx in advance for your help!!

demo.xls

~Dave

This worked great! However, the way it is setup now I will have to have a row for each route even if it is not available because if there is not a row for it, it will find the next time that route is listed. for example... if the following is my "drug book"

ibuprofen IM 5
SQ 6
PO 7
tylenol IM 8
IV 9

and I enter

ibuprofen, IV

not knowing that there is not an IV form of ibuprofen

it will return

9

because that is the data associated with the next "IV" found in the list even though it is not truely associated with ibuprofen.

Is there a way to ensure that the 2nd column choice is truely associated with the 1st column choice?
If not I can simply enter a row for each route and leave it blank or enter a warning in the "drug book".

Any ideas on the second question I had in my original post?

Thanx again!!
~Dave