Formula Needed For Matching Multiple Criteria

[richp803]

I have a spreadsheet with two tabs test and TotalMetro

test tab

A B C D E

1 Quick Report Summary
2 Data Report Month:	Last 3 Months
3 Days & Dayparts:	Mon-Sun 6a-mid
4 Demo:	A12-17
5 Created:	4/8/2013
6
7 Stations
8 Market	Station	Owner Group	Format	Population
9 Abilene, TX				74,400
10 Akron, OH
11 Albany, GA

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

I need to use the data in B4 (in this case A12-17, but this will be different from time to time), to find a match in the second sheet. In this case it will be the values in "C" (other times it could be D, etc, based on B4 from the first sheet)

TotalMetro

A B C D E F

4Mkt	Market	A18-49	Total Persons 12+	W18-49	A35-64
5a	Abilene, TX	74,400	139,900	35,200	59,400
6b	Akron, OH	293,900	602,800	149,000	288,000
7c	Albany, GA	70,200	136,500	36,300	63,000

<colgroup><col><col><col><col span="3"></colgroup><tbody>
</tbody>

I then need the name from the first sheet column "A" to match the names from the second sheet Column "B" and return the correct population in column "E" on the first sheet.

For if i were looking at Abilene in both sheets for A18-49, i would need 74,400 returned to E9 on the first sheet.
if i were looking at Akron i would need 293,900.

Depending on the market and demographic, i need the correct population from sheet two returned to sheet one.

I was able to write this for column "E", but i identified the demo before hand and repeated the formula for 30 other tabs. i would like one formula that looks at B4 and the other variables and fills in the data.

=SUMPRODUCT((TotalMetro!$B$4:$B$287=$A9)*1,TotalMetro!AH$4:AH$287)


Any help is greatly appreciated.

thanks
Rich