[Excel] City & state dropdowns

[WorkHorse]

This is for Excel 2010.

I basically just need to make a sheet that allows the user to select a City and State and displays the value for that city.

I have a worksheet that has a list of cities and states and matching values. Column A has a state and column B has a city that is in that state. Column C has some value associated with that city. So column A can have the same state many times because there are many cities in a state.

In another worksheet (same workbook) I need to have a dropdown that has the list of available states. So the dropdown options should be all of the unique state values from column A.

When the user selects an option in the State dropdown, I need another dropdown that has the list of cities that match that state. So the dropdown options should be all of the city values from column B where the state in column A matches the State dropdown selection.

When a valid State and City are selected, another cell displays the value from column C that matches that city and state.

I can’t use VBA on this project. I can use named ranges. But I’m not sure what formulas I need to get the dropdown lists I’m looking for.

[pike]

WorkHorse
try..=OFFSET(Data!$B$1,MATCH(B2,Data!$A$2:$A$290,0),0,COUNTIF(Data!A2:A290,$B$2),1)
in the example below

[WorkHorse]

I'm not sure what I am supposed to do with that. The 'Unique' column has a list of unique states, which is sort of on the right track (but it would require another sheet to hold that data). I need a dropdown, so I need to set the List formula. What formula do I need in for the List for the City dropdown validation?

[pike]

WorkHorse

In the attached zip file the validation list is in cells B2 and C2 .. with a value from a thrid column in D2