Match multiple multiple criteria and Between dates

[Nemo74]

Hello all,
I'll get right to the point...
I have 2 sheets
Sheet1

	A	B	C
1	Bill Murry	05/14/2007	WI
2	Bill Murry	02/20/2008	TX
3	Bill Murry	11/12/2008	NY

<tbody>
</tbody>

On my second sheet I have

	A	B	C	D
1	Bill Murry	03/02/2007	08/04/2007	**Formula** = WI
2	Bill Murry	02/15/2008	09/15/2008	**Formula** = TX
3	Bill Murry	11/15/2008	12/31/2008	**Formula** = ""

<tbody>
</tbody>

What formula can I put in Sheet 2, Col D to pull back that answer?

I tried a {Match(data & data, range & range, 0)} and an index match (if)) for a date range but I'm not any closer.

Thanks for your help. This has been a fun one. I'm open to anything. If I can't I'll have to rearrange a lot of data to try to make it work.


THANKS!

 

[Nemo74]

Nvm I got it with a slightly different approach,

Code on Sheet2 D1

{=INDEX('Sheet1'!$C$1:$C$3,MATCH(1,IF('Sheet1'!$A$1:$A$3=A1,IF('Sheet1'!$B$1:$B$3<=B2,1)),1))}

So yeah