Search and return text that matches a reference

Hi, hoping someone can help me with a small query, it does not seem to complex but I am a novice when it comes to using spreadsheets. I will try and make the details as clear as possible.....

**AMENDED**

IN WORKSHEET(1) CELL (B1) I WILL BE ENTERING A LETTER ANY LETTER, I THEN WANT TO LOOK THIS LETTER UP IN WORKSHEET(2) IN
A COLUMN AND WHEN THIS SAME LETTER IS FOUND I WANT TO RETURN THE TEXT IN THE ADJACENT CELL IN WORKSHEET(2) TO CELL (A1) IN WORKSHEET(1).

WORKSHEET(1) CELL (B1) HAS THE LETTER Z;
LOOK FOR THIS LETTER Z IN WORKSHEET(2) COLUMN B1:B5 AND FIND THE LETTER Z SAY IN B1 IN WORKSHEET(2);
THEN RETURN THE TEXT IN CELL (A1) IN WORKSHEET(2);
PLACE TEXT FROM WORKSHEET(2) (A1) IN WORKSHEET(1) CELL (A1).

HOPE THIS IS CLEARER.....

THANKS FOR ANY ASISTANCE.

It sounds like you want to put Z in sheet1!A1, have a formula look for Z in column B of sheet2, then replace Z in sheet1!A1 with the appropriate value from sheet2. That can not be done as placing a value in a cell that has a formula will erase the formula and placing a formula in a cell that has a value will erase that value. You could use a formula where the lookup value, "z", is hard coded like:

Please Login or Register  to view this content.

Here is a sample file:Book1.xlsx
Let me know if you have any questions.

Just happened to look back here and see the amendment to your post. I tested brokenbiscuits' formula and it does what you want.