Lookup up values in multiple possible columns based on a list of rows and a list of column

Excel Gurus,

I need some help. I'm trying to create a power and cooling calculator that imports the most current data from the department of energy electricity costs by state and sector table and then lets me use drop downs in order to create a multidimentional lookup.

source:
http://www.eia.gov/electricity/month...?t=epmt_5_06_a

I use that URL and grab the main table in a web query into a dedicated sheet in a workbook, set to update on file open.

From there I have a cover sheet that performs a list from the items in the census division (column A) in one cell and a list of the sectors across the top from a third sheet. (I chose to create a list on a third sheet so I don't have double spaced responses in the drop down that I get when I try a horizontal list validation on import for the energy sectors)

Anyhow

What I'm going for here on the cover sheet is drop down and select maryland. Then drop down and select Industrial. And the third cell return the cost of electricity based on maryland's current Industrial cost per kWhr as an example.

Thanks in advance for any help the group can provide.

Provide the sample workbook and the wanted result bro... we don't do imagining the problem
thank u
Azumi

I'd use Data->Data Validation for the drop downs.

#1 Choose List, and the list of States & Regions for source.

#2 Choose List, and the list of Sectors for the source

for the kWhr, INDEX(..MATCH( can retreive it.

INDEX(the table of kWhrs,MATCH(drop down #1, the list states & regions to the left of the table, 0),MATCH(dropdown#2,list of sectors at top,0))

In my data pull from the website, with drop down #1 in B3, drop down #2 in C3, and the kWhr in D3 I put:

=INDEX(Sheet1!B4:F65,MATCH(Sheet2!B3,Sheet1!A4:A65,0),MATCH(Sheet2!C3,Sheet1!B1:F1,0))


kWhr.xlsx