Possible Nested Vlookup

[geospatial]

I am using Excel 2007 along with xcelsius. I have 2 drop down menus. One contains the months of the year and the other contains 7 states. When I choose January it puts a "1" in A1, february a "2" in A1 and so on. For my 7 states it does the same thing except puts a number in B1. So for missouri it would be a "1" in B1 for Nebraska a "2" in B1 and so on.

Below that I have a row 2 in which each cell is going to pull in different data. My formula right now looks like this.

=IF(AND($A$1=1,$B$1=1),B12,"")&IF(AND($A$1=1,$B$1=2),B29,"")

This goes through until I hit A1=1,B1=7 and then goes into A1=2,B1=7 and so forth. When I finally get to A1=6 I start to get errors. Im guessing maybe I hit my limit?

Just wondering if there was something to simplify this process. Thought maybe a nested VLOOKUP but not sure.

 

[BrandonWLH]

Just use the data validation drop down list.

Make a list of the states and a list of the months on 1 sheet in the background.

Name the ranges like MonthList, StateList

Then in Data validation, pick List, then for the data range you jsut type in =StateList or =MonthList and then that cell will have a drop down list which will put the actual text and not the index number of the list item.

 

[geospatial]

That wouldnt help with the formula. The only thing that would change would be my formula would now be =IF(and($A$1="January,$B$1="Missouri"),B12,"")

I think I would still have the same error by the time I got through June.

This formula is pulling data that automates my charts. For instance if it is January and Missouri Cell A2 would show 350, but if it was February and Missouri A2 would show 320. My chart is reading cell A2, so as the data is chosen my chart will automatically change.