How to put reference values in from one sheet to other sheet

Dear sir please guide me,
How to put values from one sheet to other sheet by the reference of 1st sheet,

I make an excel sheet and in U column I required Name of T.S.Os (Territory sales Officer), I have written these names and their towns in sheet 2. Now I want to put Formula in cell U9 . Which put automatically by seeing the city of t.S.Os which is in B column,
Sorry my English is so poor,
But if you understand my Problem, Please solve this as soon as possible,
Sir please see attachment.

Isn't this very similar to your other thread?

You can put this formula in U9:

=IF(COUNTIF(Sheet2!$A$6:$W$26,B9)=0,"",INDEX(Sheet2!$A$5:$W$5,SUMPRODUCT((B9=Sheet2!$A$6:$W$26)*(COLUMN($A:$W)))))

then copy down.

Hope this helps.

Pete

Thanks Sir,
My Problem is solved, I pray to God for your long Life,
Again thanks.

Thanks sir for reply,
Next Time I will do Iit as you guide me. But now my problem is solved,
Again Thanks,

That may have solved your problem, but you still have a massive IF formula in column V. Instead of that, you can use this formula in V9:

=IFERROR(LOOKUP("zzzz",Sheet2!$A$4:INDEX(Sheet2!$4:$4,MATCH(U9,Sheet2!$5:$5,0))),"")

then copy down.

Hope this helps.

Pete