Lookup with two criteria including date range

[Adriana Hancock]

Hello,

I know there are posts out there with this same question, but the responses are beyond my understanding of Excel Formulas .... while I consider myself pretty good at Excel, you all are like astrophysicist/brain surgeon-level Excel experts.

I have two sheets of data. On Sheet1 I have employees with a start date and end date of their compensation records. On Sheet2 I have the employees listed with a start and end date for their job information records.

Onto Sheet1, I need to pull in the applicable "grade" from Sheet2, for the appropriate employee and where the Comp Info start date (from Sheet1) falls between the start and end dates on Sheet2.

I am hoping someone is able to help me soon with this formula. I have been working on it for days.

Thank you!

https://www.dropbox.com/s/xhkaie41mfi4sxx/test_bonus_2015_v1.xlsx?dl=0

 

[tomas]

Hi

Should be working

 

[jayalaxmi]

I don't know y but the file is getting opening in Read only format??

Regards,
Jaya

 

[Deepak]

i didn't understand the logic but managed something this..


D2=INDEX(Sheet2!$D$2:$D$41,MATCH(1,INDEX((Sheet2!$A$2:$A$41=Sheet1!A2)*(Sheet2!$B$2:$B$41<=Sheet1!B2)*(Sheet2!$C$2:$C$41>=Sheet1!C2),,),0))

but still throwing error in some cases.