Okay, since the first two columns of each table are in fact not
identical, we'll change strategies, somewhat. :)

First, define the following references...

Insert Name Define

Name: Officer
Refers to: =Sheet1!$A$3:$C$8

Click 'Add'

Name: Partner
Refers to: =Sheet1!$F$3:$H$8

Click 'Add'

Name: SP
Refers to: =Sheet1!$K$3:$M$8

Click 'Ok'

Now, use the following formula...

=INDEX(INDIRECT(L19),MATCH(1,(INDEX(INDIRECT(L19), 0,1)=K19)*(INDEX(INDIRE
CT(L19),0,2)=MAX(IF((INDEX(INDIRECT(L19),0,1)=K19) *(INDEX(INDIRECT(L19),0
,2)<=N19),INDEX(INDIRECT(L19),0,2),MIN(IF(INDEX(IN DIRECT(L19),0,1)=K19,IN
DEX(INDIRECT(L19),0,2)))))),0),3)

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!