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.

Try this array formula**:

=IFERROR(INDEX(Sheet2!D$2:D$7,MATCH(1,IF(Sheet2!A$2:A$7=B2,IF(A2>=Sheet2!B$2:B$7,IF(A2<=Sheet2!C$2:C$7,1))),0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

I think this is what you are looking for

WFM007,
The formula should have returned Heather in cell C5 and Jen in cell C8 which is not the case.

Hi Rmitri,

As per your post, if date & client # matches then it should return to "CATHY".

Originally Posted by rmitri

WFM007,
The formula should have returned Cathy in cell C5

I think the correct result should be Heather.

That's what my suggestion returns.

Here's a small sample file that demonstrates this...

rmitri.xlsx