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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Punch Judy[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td][/td][td]cat and dog[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td][/td][td][/td][td]apples & pears[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td][/td][td][/td][td]Heart of stone[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet XYZ[/td][/tr][/table]

Returns this (formula is in cell B5 )
Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]cat and dogs[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]apples & pears[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Punch & Judy[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]heart of gold[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td=bgcolor:#C6E0B4]apples & pears[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]

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