Finding a column value based on date and column header

Hi,
I need a formula that will return the value in a cell value for a certain date. For example,

On Sheet 2 columns headers are week ending values, Column B is 8-Jan-12, Column C is 16-Jan-12, etc.
Column A are names of products.

On Sheet 1, I want to populate a corresponding cell in Sheet 1 for the product based on the date entered by the user on Sheet 1.

Attached is an example

Based on the date entered in B1 on Master-Weekly sheet, I want to populate cells in column D (Item Weekly Ending Usage) with the value from Weekly_new Sheet for the respective Brand.

if(date in B1 is same as date in sheet Weekly_new for Brand in Master-Weekly_A5) populate Master-Weekly_D(Brand) with that value.