Search a phrase in a sheet then display that phrase in the cell

[Jazzzola324]

Hello,
I am trying to figure out a formula which will search a separate sheet for four different phases. Then, display the one phrase that is in the sheet.

I need to search the exact phrase and the cells it is search will only contain that phrase ( i.e. I’m not search a paragraph of text)

I tried combining IF and INDEX, but that didn’t work.

any help you can give will be much appreciated

 

[Yongle]

With the 4 phrases in A1:A4 in sheet containing the formula, and searching for phrases in Sheet XYZ

=IF(COUNTIF('Sheet XYZ'!1:1048576,A1)>0,A1,
IF(COUNTIF('Sheet XYZ'!1:1048576,A2)>0,A2,
IF(COUNTIF('Sheet XYZ'!1:1048576,A3)>0,A3,
IF(COUNTIF('Sheet XYZ'!1:1048576,A4)>0,A4,"NOT FOUND"))))

Search this sheet

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​
1​
2​	Punch Judy
3​		cat and dog
4​			apples & pears
5​				Heart of stone
6​
7​

Sheet: Sheet XYZ

Returns this (formula is in cell B5 )
Excel 2016 (Windows) 32 bit

	A​	B​
1​	cat and dogs
2​	apples & pears
3​	Punch & Judy
4​	heart of gold
5​		apples & pears

Sheet: Sheet2

Note
or if preferred place phrases in the formula like this...

=IF(COUNTIF('Sheet XYZ'!1:1048576,"my phrase 1")>0,"my phrase 1",
etc