Hi,

I can't seem to figure out how to generate a drop down list with V lookup.

My situation is like this.:

In sheet1 I have three colums: Projectcode, Project, Location
The projectcodes generally correspond with only 1 project and 1 location, so I've created an array in sheet2 with the projectcodes and their corresponding projects and locations.
Like this, I can enter a projectcode (say in A1) in sheet1 and with VLOOKUP the corresponding project (in B1) and location (in C1) are automatically entered. This works perfectly.

Now here comes the problem, there are a few projectcodes (5 out of 25) that actually cover 2 or more locations. So instead of 1 value in sheet1 C1, I would like a dropdown list in C1, based on the array I made in Sheet2.

In sheet2 I made the following array:
The first column lists all the projectcodes
The second column lists the projects
The third column is a list of single values for location or dropdown lists of locations based on column 4-15. I have created the dropdown lists manually with data validation - list - range:column 4-15
Colums 4-15 represent all the possible locations, when a project code corresponds with the location (of one particular column), the value in that column is the name of the location. If the project code does not correspond with the location the cell is left empty.

Since I am only using 25 projectcodes, it is not a problem to create the dropdown list manually in sheet2.
But in Sheet1, I will likely need over a thousand entries, so I need a uniform formula that returns single values for a location (20 out of 25 cases so), but also dropdown lists if needed (those 5 out of 25).

Sorry to not have an example posted, I hope it is clear anyway.
Can anyone guide me?