Formula to Dynamic Search Text within range of cells

[CM214]

Hello guys,

Is there a way Excel can search for a specific text within a selected range of cells? I want to Excel to find a specific text, say something as "#1" within a cells A1:J20, and no matter where i place "#1" within A1:J20, Excel manages to find that text and return it within a different section of the sheet.

I can be more descriptive in what I am trying to get Excel if I need to further explain.

 

[Peter_SSs]

Would "#1" be the only thing in the cell(s) it is in or might a cell contain "Tom is #1 this week"?

In the formula cell you want just "#1" returned if it finds "#1" any where in A1:J20? If so, try this

21 06 14.xlsm
	A	B	C	D	E	F	G	H	I	J
1	data	data	data	data	data	data	data	data	data	data
2	data	data	data	data	data	data	data	data	data	data
3	data	data	data	data	data	data	data	data	data	data
4	data	data	data	data	data	data	data	data	data	data
5	data	data	data	data	data	data	data	data	data	data
6	data	data	data	#1	data	data	data	data	data	data
7	data	data	data	data	data	data	data	data	data	data
8	data	data	data	data	data	data	data	data	data	data
9	data	data	data	data	data	data	data	data	data	data
10	data	data	data	data	data	data	data	data	data	data
11	data	data	data	data	data	data	data	data	data	data
12	data	data	data	data	data	data	data	#1	data	data
13	data	data	data	data	data	data	data	#1	data	data
14	data	data	data	data	data	data	data	#1	data	data
15	data	data	data	data	data	data	data	#1	data	data
16	data	data	data	data	data	data	data	data	data	data
17	data	data	data	data	data	data	data	data	data	data
18	data	data	data	data	data	data	data	data	data	data
19	data	data	data	data	data	data	data	data	data	data
20	data	data	data	data	data	data	data	data	data	data
21
22	#1	#1
Search

Cell Formulas
Range	Formula
B22	B22	=IF(COUNTIF(A1:J20,A22),A22,"")