Excel Data Validation Drop-Down using a Lookup

Question

For some reason I can't seem to find an example for this.

I have a table with two columns, [County] and [Street].

I have another table with many columns, two of which are [County] and [Street]. On this second table, each row will already have [County] filled in, but I need to allow the user to click on the [Street] cell for each row and select (from a drop-down menu), a Street. The Streets in the drop-down must dynamically change to only show the Streets from the first table that are included in the County on that particular row on the Second Table.

Table 1:

County   |  Street
-------------------
County 1 | Street 1
County 1 | Street 2
County 1 | Street 3
County 2 | Street 4
County 2 | Street 5
County 2 | Street 6

Table 2:

County   |   Street   |  Column 3  |  Column 4 
------------------------------------------------
County 1 |{Drop-down} |  --------  |  --------
County 1 |            |  --------  |  --------
County 1 |            |  --------  |  --------
County 2 |            |  --------  |  --------
County 2 |            |  --------  |  --------
County 2 |            |  --------  |  --------

The best conditional data validation example I can find is to use INDIRECT and point the validation to a pre-created list of Streets for each County. That's not a good option because this data set is very large and is updated often. I would like to use some kind of INDEX-MATCH array lookup to dynamically create the list from the original data set (Table 1), but I keep getting errors when I try to use a lookup formula.

Please keep answers to non-VBA if possible. This workbook may need to be used in applications without VBA enabled.

Thanks for your help!

teylyn teylyn · Accepted Answer · 2016-01-22T00:13:57

With the lookup table on Sheet2, sorted ascending by column A, and the drop-down on Sheet1, do this:

Select Sheet1!B2 (the first cell where the Street drop-down will go)
create a new named range with the Name Manager, call it "StreetList" and use this formula:

=INDEX(Sheet2!$B:$B,MATCH(Sheet1!$A2,Sheet2!$A:$A,0)):INDEX(Sheet2!$B:$B,MATCH(Sheet1!$A2,Sheet2!$A:$A,1))

It is important that you select the correct cell before writing the formula, because it contains a relative reference to the cell on the left.
apply data validation to cell Sheet1!B2 using the List option and the named range =StreetList as the source
copy the data validation cell down.

When the active cell is next to "County 1", the dropdown contains only the streets for that county.

With that cell B2 selected, open the Name Manager, click the formula for StreetList and see the marching ants around the result of the formula.

Excel Data Validation Drop-Down using a Lookup

1 Answers