Creating Dependent Lists from a Column Lookup in an #Excel List by David Hager

When you have columns in a list that contain (for instance) state names and their corresponding cities, you might find it useful to create lists from the internal range of the subordinate column. For example, in the list shown here you might want to return the range from the City column corresponding to a specified state in the State column shown below.

 LookupFromTable1

The key to generating lists from the City column is the following formula.

TheCity=OFFSET(State,MATCH(TheStateName,State,0)-1,-1,MATCH(TheStateName,State,1)-MATCH(TheStateName,State,0)+1)

where State is the column in the list on the LookupSheet tab containing state names and

where TheStateName is the cell containing a data validation list of unique states (Control!C3).

where TheCityName cell (Control!D4) contains a data validation list of cities corresponding to the return value of TheCity.

Basically, what this formula does is return a range from the City column, given a specified state.

The following figure shows the cells with the data validation drop down lists.

 LookupFromTable2

The information from these cells can be used for further lookups, which is the subject of an upcoming post. You can download the example file here.

LookupFromTable

 

Advertisements

2 thoughts on “Creating Dependent Lists from a Column Lookup in an #Excel List by David Hager

  1. Pingback: Get Information from #Excel About the August 2017 Total Eclipse by David Hager | Excel For You

  2. Pingback: #Excel Super Links #78 – shared by David Hager | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s