Utilizing Match & Index with two named ranges

I'm almost there, just need a little help from the masters. This is my first post so please guide me in the right direction if I'm doing something wrong.

Column A contains State names; column B contains a few cities for each of the states. Row 1 contains rank as the header row. Then the table begins in cell C2 with the rates. I have two of these (on separate sheets), one titled w dep (with dependants) and the other w-o dep (without dependants).
On a third sheet I have 4 combo boxes.
1. Chooses between Dep or W-O Dep
2. Chooses Rank
3. Chooses State
4. Chooses City
Here are my questions.
• How can I filter down the cities so that if I chose Texas for example, only the cities from Texas would show up in my combo box?
• I have figured out the formula to display the results from the w dep sheet but cannot get the information from the w-o dep sheet. Here is the formula I’m using
o =IF(C4=2,INDEX(DepIndex,MATCH(D13,INDEX(DepIndex,,1),0),MATCH(D7,INDEX(DepIndex,1,),0)),IF(C4=3,INDEX(NoDepIndex,MATCH(D13,INDEX(NoDepIndex,,1),0),MATCH(D7,INDEX(NoDepIndex,1,),0)),""))
o Where C4 is the Cell link I’m using.
Any help is greatly appreciated! I’ll also attach the spreadsheet I’m working with as a guide.
Thanks in advance!

As far as the index/match formula is concerned...

your headings in C1:Z1 of the w-o dep sheet have an extra space at the end... delete those extra spaces and your formula works.

To create the dependent lists using comboboxes...you will have to redesign your sheet a bit and also use VBA..

... See here:

http://contextures.com/xlDataVal11.html

it is easier to do this strictly with Data Validation... the only thing is you won't see the drop-down arrow until you click the cell..

http://contextures.com/xlDataVal02.html