Can a VLOOKUP nest in an IF formula? Can I use both in a data validation drop down??

[mltornroth]

I'm trying to figure out how to get around the nested function restriction in a formula that's being used to apply data validation to a cell that results in a drop down list. The problem I think I am having is that the result for some values is a single value, while for others it is a small range of values.

Example:

CellA1 has a drop down of possible drug name choices. Based on the selection in A1, B1 should be the corresponding dosage(s) for the drug selected. There are 13 drugs total and 10 of those only have one possible dosage associated with it. For those a vlookup would work perfectly; however, the other three have 2 or 3 possible dosages associated to them so the result should be a drop down selection of those doses if any one of those 3 drugs are selected.

I've tried defining half of the formula and then adding it as one function to evaluate but that didn't work. Does anyone know how to do this? Can I combine an IFERROR formula with a vlookup so that that the formula tries to match the target with one of those 3 drugs and if successful, it returns the drop down range that corresponds with the selection, but if false it resorts to the vlookup for the rest of the drugs?

Thanks

[Fotis1991]

Hi

Can you upload a small sample workbook?

[mltornroth]

Sure. The dosages and the drugs they align with can be found on the data tab. I separated the ones with multiple dosages because I was playing around with naming the ranges and trying to figure something out. The target cell that will dictate the dosage is G6 on the form tab. I am trying to have the result on T6.

Gilead Special Exception REVIEW3.xlsmThanks for taking a look!

[galvinpaddy]

sry wrong thread

[Fotis1991]

Unfortumately, my poor English, does not allows me to understand, what happens in your Sheets...Sorry...