VLOOKUP and Data Validation

should work with a dropdown OK

see row 9 on attached sheet

Further to etafs response you may want to modify the vlookup formula itself to:

=IFERROR(vlookupbit,"")

'vlookupbit' is you current formula, this will then leave the cell blank when a match is not found or in your case when the CLIN cell is blank (before an item is selected) otherwise you will have #N/A displayed.

your welcome
if my assistance has helped, and only if you wish to , there is a reputation icon * on the left hand side - you can add to my reputation here

use this
=VLOOKUP(A9,DATA!A3:C52,3,FALSE)

you can add the iferror posted by Harribone also if it does find the entry but nothing is in the version , you will get 0 - what do you want if zero ?
you can add another IF to test for zero 0 and put what you would prefer to have in column C

each CLIN ID only has one option in column C - or do you just want a dropdown list with all the options in ?
can you give some examples

OK - didn't see the dropdown in C
probably need to setup a dependant list
like here
http://www.contextures.com/xlDataVal02.html

i'm in UK timezone - so if noone else replies, I will see if can have a look tomorrow
you probably need to breakout the lists - onto the data sheet -

I think you may need a VBA solution otherwise quite a complicated dropdown validation list
and - and spaces are not allowed in names and so a substitute will need to be used
i can have a look at, but may take a few days

Hi,

Here is my contribution to your problem. I've taken etaf's file and done some changes (i think this is what you are looking for).
In Col C of the DATA tab i've removed all the drop down lists. then I've created three example lists which have been give a name for the range (let me know if you need help on naming a range). Then in col C for the first three items you enter the name of the list that should appear for that item.

On the MAIN tab i've used data validation to show a list where the formula is =INDIRECT(IFERROR(VLOOKUP($A9,DATA!$A$3:$C$52,3,FALSE),""))
Basically this looks up column C for the chosen item which with the first 3 items is ListA, ListB and ListC and then the INDIRECT bit does some magic and basically tells excel to use this as the range reference for your list.

Hopefully that made sense and you can apply this method to your work, if you have any issues setting this up then let us know.

EDIT Forgot to point out that the values in Col C of your DATA tab must match to the name of the range you want to show in you drop down, note you can't have spaces and can't call them something like A1 as that is a cell reference.