Lookup formula based on a condition and date timeframe

In an Excel workbook I have the following Sheets:

Sheet1 contains in addition to other data, client purchase information:

PurchaseDate ClientNo. AccountRep
31-Jan-2012 1 ?
23-Feb-2012 7 ?
17-Mar-2012 24 ?
09-Apr-2012 77 ?
02-May-2012 89 ?
25-May-2012 103 ?
17-Jun-2012 304 ?

Sheet2 contains a list of Account Reps responsible for the account through time
Client# StartDate EndDate RepName
304 02-Jan-2011 01-May-2011 Tim
304 02-May-2011 31-Dec-2011 John
304 01-Jan-2012 01-Aug-2012 Jen
77 02-Jul-2011 01-Nov-2011 Jack
77 02-Nov-2011 31-Dec-2012 Heather
77 01-Jan-2012 01-Jul-2013 Cathy

I would like to write a formula in sheet 1 under Account Rep to identify the appropriate Account Representative in sheet2 based on the account number and the date of the transaction.