Return text based on matching two columns criteria

[benoj2005]

Anyone able to think of a formula that based on Period and name to return the area.

So for example if I had Tom and period 2 as the criteria, it should return area = West

I'd normally use a sumproduct on this sort of thing but because its text I want to return and I need some greater than and less than in there its a new one on me.

Name	Area	From Period	To Period
Tom	West	1	5
Tom	South	6	7
Tom	West	8	12
John	North East	1	2
John	North West	3	7
John	South West	8	12

[samba_ravi]

Try
=lookup(2,1/(($b$3:$b$8=$i3)*($d$3:$d$8<=$h3)*($e$3:$e$8>=$h$3)),$c$3:$c$8)

[benoj2005]

Try
=lookup(2,1/(($b$3:$b$8=$i3)*($d$3:$d$8<=$h3)*($e$3:$e$8>=$h$3)),$c$3:$c$8)

Thankyou! Whats the logic here?