sorry, mistake of my earlier function
should be
=INDEX(OFFSET(Sheet1!$B:$B,0,MATCH(B$1,Sheet1!$1:$ 1,0)-MATCH
("Agent#",Sheet1!$1:$1,0)),MATCH($A2,Sheet1!$B:$B, 0))



another way:

in Sheet2!B2
=INDEX(Sheet1!$A:$C,MATCH($A2,Sheet1!$B:$B,0),MATC H(Sheet2!B$1,Sheet1!
$1:$1,0))