Compare two column data with another two columns

[vishallawati777]

Hi all,

Having query regarding Compare two column data with another two columns

E.g.

In Sheet 2 column
[TABLE="width: 178"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]Column 1
[/TD]
[TD]Column 2
[/TD]
[/TR]
[TR]
[TD]Germany
[/TD]
[TD]Orange
[/TD]
[/TR]
[TR]
[TD]Germany
[/TD]
[TD]Apple
[/TD]
[/TR]
[TR]
[TD]India
[/TD]
[TD]Grapes
[/TD]
[/TR]
[TR]
[TD]Brazil
[/TD]
[TD]Pineapple
[/TD]
[/TR]
[TR]
[TD]India
[/TD]
[TD]Orange
[/TD]
[/TR]
[TR]
[TD]Maldives
[/TD]
[TD]Grapes
[/TD]
[/TR]
[TR]
[TD]Myanmar
[/TD]
[TD]Apple
[/TD]
[/TR]
[TR]
[TD]Nepal
[/TD]
[TD]Orange
[/TD]
[/TR]
[TR]
[TD]Romania
[/TD]
[TD]Pineapple
[/TD]
[/TR]
[TR]
[TD]Sri Lanka
[/TD]
[TD]Apple
[/TD]
[/TR]
[TR]
[TD]United Kingdom
[/TD]
[TD]Pineapple
[/TD]
[/TR]
[TR]
[TD]Yemen
[/TD]
[TD]Apple
[/TD]
[/TR]
</tbody>[/TABLE]

In Sheet 1 with using formula for Comparing with below column with formula =IF(COUNTIFS(Sheet2!A:A,A3,Sheet2!B:B,B3),"Yes","No").

However I also want Yes for below bold column (e.g. In Germany: Orange, Grapes, Pineapple present and in sheet 2 Germany: Orange present. Both are not duplicate but Sheet 1 Germany contains Orange. Hence I also want Yes for same.

please help me on this.
[TABLE="width: 339"]
<tbody>[TR]
[TD][/TD]
[TD]
[TABLE="width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]After formula
[/TD]
[/TR]
[TR]
[TD]Country
[/TD]
[TD]Fruits
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Germany
[/TD]
[TD]Orange, Grapes, Pineapple
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]Myanmar
[/TD]
[TD]Grapes
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]Germany
[/TD]
[TD]Pineapple
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]India
[/TD]
[TD]Grapes
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]Germany
[/TD]
[TD]Orange, Apple
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]Italy
[/TD]
[TD]Pineapple
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]India
[/TD]
[TD]Apple
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]Brazil
[/TD]
[TD]Pineapple
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]Yemen
[/TD]
[TD]Grapes
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]United kingdom
[/TD]
[TD]Orange
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]Sri Lanka
[/TD]
[TD]Apple
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]United kingdom
[/TD]
[TD]Grapes
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]Yemen
[/TD]
[TD]Pineapple, Apple
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]United kingdom
[/TD]
[TD]Apple
[/TD]
[TD]No
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[vishallawati777]

There is also another formula for single column for containing word
=SUMPRODUCT(--ISNUMBER(SEARCH($B$2:$B$17,B3)))>0

which will solve my problem for Single column only. But need help for both column or multiple column.