Search in each column in Sheet1 and return value from column A Sheet1 to Sheet2

[ptk]

In Sheet 2 cell B3, I want to be able to type in a search string ie "CF" and extract the information from Sheet 1 column A on the same row "CF" occurs for each column in Sheet 1. This should be done for each column that "CF" occurs in Sheet 1. I have used LOOKUP like this:

Please Login or Register  to view this content.

but it returns faulty values.
Other characters are involved in the cells like "," and elevated text, perhaps that is the issue?

I have attached a sample file with an example in sheet 2.

Here is another EXAMPLE:
If I type in "AB" in Sheet 2 cell B3, it should return the values (using the attached sample)
Text 6 (from [sheet 1 A9])
Text 8 (from [sheet 1 A11])
Text 4 (from [sheet 1 A7])
Text 6 (from [sheet 1 A9])

All comments are welcome! Thank you for your help.

Excel 2003 PC