Thanks Jim,
your code worked for me . Though it is a bit complex but i will try to
understand it. Also What is the difference beyween using your code and
using Ardus Suggestion which is--- =HLOOKUP(B$1,Sheet1!B$1:D$5,ROW(),0)


By all ways thank you again.