Macro that could return a value if criteria falls between date ranges

Hi All,

I've been running up and down regarding this topic and cant seem to solve my problem. The thing is I need a macro that If a date falls within a range in column Start Date and End Date in Sheet1, it would return an Amount value in Sheet2. Please see attached image file for reference.

Thanks in advance!

Hi Oeldere,

Thanks for the advice. I don't know who to contact to so I have tagged Excel formula category. Anyhow, I'm illustrating it below

Sheet1:
Staff No scale type Amount Crcy Start Date End Date
10545245 Default 380,900.00 PHP 4/26/2014 12/31/2014
10545245 Default 380,900.00 PHP 12/3/2013 4/25/2014
10545245 Default 380,900.00 PHP 10/1/2013 12/2/2013
10545245 Default 380,900.00 PHP 9/1/2013 9/30/2013

Sheet2:
Staff No Start Date AMOUNT
10545245 4/21/2013
10545245 10/22/2013
10545245 4/23/2013
10545245 4/24/2013

I need a macro or a formula wherein if a date entered in StartDate in Sheet2 falls under a date range in Sheet1(StartDate-EndDate), it will locate the Amount value in Sheet1 and display it in Amount col in Sheet2.

Thanks for the help.