Find text and then show text

[Obewan]

In column A I have a list of all different words and name but the main issue is this - this appears in cell A113 but also there will be other names in this column - this is the name of a dog I am trying to replicate in another column (i.e. the bold text)
WILLIAM BALEBKW / D Jul-21 Allen Deed x Dyna Chill

I have it's name in column AR (i.e. AR13) - but the row should be irrelevant as long as it's within the range - i.e. rows 2:4250
WILLIAM BALE

So what I am trying to do is write a formula that says:
- if the text in column AR2:AR4250 can be found in columns A2:a4250 can it write that name in column X - therefore at X113 it would answer WILLIAM BALE

They are on completely different row so I need to cover rows 2:4250 and be able to drag the formula down row X from 2:4250

Hope that makes sense?

rergards

Seamus

 

[Muhammad_Usman]

Receip.xlsm
	G	H	I
1	Data	Criteria	Out Put
2	WILLIAM BALEBKW / D Jul-21 Allen Deed x Dyna Chill	WILLIAM BALE	WILLIAM BALE
3	WILLIAM / D Jul-21 Allen Deed x Dyna Chill	Sanke
4	D Jul-21 Allen Deed x Dyna Chill	Allen Deed	Allen Deed
5	D Jul-21 Allen Deed x Dyna Chill	Dragon
2023

Cell Formulas
Range	Formula
I2:I5	I2	=IF(ISNUMBER(SEARCH("*"&H2&"*",G2)),H2,"")

 

[Obewan]

Close but needs to work where the Criteria is on a different row to the Data - i.e. somewhere between row 2 to 4250. The Out Put should be on the same row as the Data

 

[Muhammad_Usman]

Close but needs to work where the Criteria is on a different row to the Data - i.e. somewhere between row 2 to 4250. The Out Put should be on the same row as the Data

an output sample data will help to batter underdatand

 

[Phuoc]

Try this

Book1
	A	B	C
1	Data	Criteria	Out Put
2	D Jul-21 Allen Deed x Dyna Chill	WILLIAM BALE	WILLIAM BALE
3	WILLIAM / D Jul-21 Allen Deed x Dyna Chill	Sanke
4	D Jul-21 Allen Deed x Dyna Chill	Allen Deed	Allen Deed
5	WILLIAM BALEBKW / D Jul-21 Allen Deed x Dyna Chill	Dragon
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=IF(ISNUMBER(MATCH("*"&B2&"*",$A$2:$A$5,0)),B2,"")

 

[Obewan]

Data	Criteria	Output
	WILLIAM BALE
	OBLIGING CYNDIE
	LET'S WIN MINDEE
	YOGI'S BABY
	VACANT BOX
	BEIJING LING
	CHIPPED PENNY
	MICK'S GAL
	Tips:
WILLIAM BALEBKW / D Jul-21 Allen Deed x Dyna Chill		WILLIAM BALE
Trainer: Juanita Thompson (Tungamull)Owner: Bradley Mulvihill
Prizemoney: $160
Rating:97
Our Price:$2.4
Date

Try this

Book1
	A	B	C
1	Data	Criteria	Out Put
2	D Jul-21 Allen Deed x Dyna Chill	WILLIAM BALE	WILLIAM BALE
3	WILLIAM / D Jul-21 Allen Deed x Dyna Chill	Sanke
4	D Jul-21 Allen Deed x Dyna Chill	Allen Deed	Allen Deed
5	WILLIAM BALEBKW / D Jul-21 Allen Deed x Dyna Chill	Dragon
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=IF(ISNUMBER(MATCH("*"&B2&"*",$A$2:$A$5,0)),B2,"")

 

[Obewan]

Minisheet.xlsx
	A	B	C
1	Data	Criteria	Output
2		WILLIAM BALE
3		OBLIGING CYNDIE
4		LET'S WIN MINDEE
5		YOGI'S BABY
6		VACANT BOX
7		BEIJING LING
8		CHIPPED PENNY
9		MICK'S GAL
10		Tips:
11
12
13
14	WILLIAM BALEBKW / D Jul-21 Allen Deed x Dyna Chill		WILLIAM BALE
15	Trainer: Juanita Thompson (Tungamull)Owner: Bradley Mulvihill
16	Prizemoney: $160
17	Rating:97
18	Our Price:$2.4
19	Date
20	MON
Sheet1

Cell Formulas
Range	Formula
C3:C13,C15:C20	C3	=IF(ISNUMBER(MATCH("*"&B3&"*",$A$2:$A$500,0)),B3,"")
B11:B20	B11	=IF(ISNUMBER(MATCH("*"&#REF!&"*",$A$2:$A$5,0)),#REF!,"")

 

[Obewan]

So column C would have the formula and answer would be in the green cell at C14

 

[Phuoc]

Like this?

Book1
	A	B	C
1
2	Data	Criteria	Output
3		WILLIAM BALE
4		OBLIGING CYNDIE
5		LET'S WIN MINDEE
6		YOGI'S BABY
7		VACANT BOX
8		BEIJING LING
9		CHIPPED PENNY
10		MICK'S GAL
11
12
13
14
15	WILLIAM BALEBKW / D Jul-21 Allen Deed x Dyna Chill		WILLIAM BALE
16	Trainer: Juanita Thompson (Tungamull)Owner: Bradley Mulvihill
17	Prizemoney: $160
18	Rating:97
19	Our Price:$2.4
20	Date
Sheet1

Cell Formulas
Range	Formula
C15:C20	C15	=IFERROR(LOOKUP(1,-SEARCH($B$3:$B$10,A15),$B$3:$B$10),"")

 

[Obewan]

Like this?

Book1
	A	B	C
1
2	Data	Criteria	Output
3		WILLIAM BALE
4		OBLIGING CYNDIE
5		LET'S WIN MINDEE
6		YOGI'S BABY
7		VACANT BOX
8		BEIJING LING
9		CHIPPED PENNY
10		MICK'S GAL
11
12
13
14
15	WILLIAM BALEBKW / D Jul-21 Allen Deed x Dyna Chill		WILLIAM BALE
16	Trainer: Juanita Thompson (Tungamull)Owner: Bradley Mulvihill
17	Prizemoney: $160
18	Rating:97
19	Our Price:$2.4
20	Date
Sheet1

Cell Formulas
Range	Formula
C15:C20	C15	=IFERROR(LOOKUP(1,-SEARCH($B$3:$B$10,A15),$B$3:$B$10),"")

Sorry I had columns in wrong order

Book5
	A	B	C
1	Data	Output	Criteria
2			WILLIAM BALE
3			OBLIGING CYNDIE
4			LET'S WIN MINDEE
5			YOGI'S BABY
6			VACANT BOX
7			BEIJING LING
8			CHIPPED PENNY
9			MICK'S GAL
10			Tips:
11
12
13
14	WILLIAM BALEBKW / D Jul-21 Allen Deed x Dyna Chill	WILLIAM BALE
15	Trainer: Juanita Thompson (Tungamull)Owner: Bradley Mulvihill
16	Prizemoney: $160
17	Rating:97
18	Our Price:$2.4
19	Date
20	MON
Sheet1

Cell Formulas
Range	Formula
B2:B10	B2	=IF(ISNUMBER(MATCH("*"&C2&"*",$A$2:$A$5,0)),C2,"")
C11:C20	C11	=IF(ISNUMBER(MATCH("*"&#REF!&"*",$A$2:$A$5,0)),#REF!,"")