Match multiple multiple criteria and Between dates

[Nemo74]

Hello all,
I'll get right to the point...
I have 2 sheets
Sheet1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Bill Murry[/TD]
[TD]05/14/2007[/TD]
[TD]WI[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Bill Murry[/TD]
[TD]02/20/2008[/TD]
[TD]TX[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Bill Murry[/TD]
[TD]11/12/2008[/TD]
[TD]NY[/TD]
[/TR]
</tbody>[/TABLE]

On my second sheet I have
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Bill Murry[/TD]
[TD]03/02/2007[/TD]
[TD]08/04/2007[/TD]
[TD]**Formula** = WI[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Bill Murry[/TD]
[TD]02/15/2008[/TD]
[TD]09/15/2008[/TD]
[TD]**Formula** = TX[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Bill Murry[/TD]
[TD]11/15/2008[/TD]
[TD]12/31/2008[/TD]
[TD]**Formula** = ""[/TD]
[/TR]
</tbody>[/TABLE]

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