from my understanding,
u have the raw data in sheet1
with column header arranging in order
Col A : AgentCity
Col B : Agent#
Col C : AgentName

then u wanted to lookup using Agent# in sheet2
where
Col A : Agent#
Col B : AgentName
Col C : AgentCity

then, given that the header is in row 1,
and with Sheet2!A:A (Agent#) manually keying in
in Sheet2!B2, key in
=INDEX(OFFSET(Sheet1!$B:$B,0,MATCH("Agent#",Sheet1 !$1:$1,0)-MATCH(B
$1,Sheet1!$1:$1,0)),MATCH($A2,Sheet1!$B:$B,0))
copy across to column C, copy down as long as u need

*u can change the "Agent#" to $A$1 if that's where the column header
is

help it helps.