Formula question. How to return a Y/N if Cell C of List 1 contains a word from another List 2

[powerpivot beginner]

Hi

I have 2 sheet in an excel.

In Sheet 1, there are 3 columns; A:Country, B:Supplier and Croducts

In Sheet 2, there are 3 columns, A:Country, B:Supplier and Croducts.

I want to have a formula where column D of Sheet 1 will return a value of Yes or No if Column Croduct of Sheet 1 is found in Product Column of Sheet 2 with same Country and Supplier.Product name does not make to be exact match.

 

[bosco_yip]

Maybe,

In "Sheet1" D2, formula copied down :

=IF(ISNUMBER(LOOKUP(9^9,SEARCH(TRIM(MID(SUBSTITUTE(","&C2,",",REPT(" ",50)),ROW(INDIRECT("1:"&(LEN(C2)-LEN(SUBSTITUTE(C2,",",""))+1)))*50,50)),LOOKUP(2,1/(Sheet2!A$2:A$8=A2)/(Sheet2!B$2:B$8=B2),Sheet2!C$2:C$8)))),"Yes","No")

Regards
Bosco

 

[powerpivot beginner]

HI bosco_yip. Thanks for your reply and advice. It worked. Will the formula continue to work if there are a long list of products on both sheet 1 column c and sheet 2 column c or it does not matter?

 

[powerpivot beginner]

Hi

Sorry, I found a problem. Product name in Sheet 1 may contains a combination
of text and number, eg BAN1 wheres the corresponding product in Sheet 2 is BAN. Since I do not need exact match, the answer should be "Yes". Current formula is returning a "No" reply.

 

[bosco_yip]

Hi

Sorry, I found a problem. Product name in Sheet 1 may contains a combination
of text and number, eg BAN1 wheres the corresponding product in Sheet 2 is BAN. Since I do not need exact match, the answer should be "Yes". Current formula is returning a "No" reply.

If "Sheet1" Product name contains text+number, try to use the post #.02 formula same logic, but in revert data value checking.

1] Select "Sheet1" D2 >> Define name >>

>> Name : MatchData

>> Refer to :
=LOOKUP(2,1/(Sheet2!$A$2:$A$8=Sheet1!$A2)/(Sheet2!$B$2:$B$8=Sheet1!$B2),Sheet2!$C$2:$C$8)

>> OK

2] In "Sheet1" D2, enter formula and copied down :

=IF(ISNUMBER(LOOKUP(9^9,SEARCH(TRIM(MID(SUBSTITUTE(","&MatchData,",",REPT(" ",50)),ROW(INDIRECT("1:"&(LEN(MatchData)-LEN(SUBSTITUTE(MatchData,",",""))+1)))*50,50)),C2))),"Yes","No")

Regards
Bosco

 

[salim hasan]

Trythis Macro

Option Explicit
Sub find_me()
Dim sh1 As Worksheet: Set sh1 = Sheets("sheet1")
Dim sh2 As Worksheet: Set sh2 = Sheets("sheet2")
Dim arr1, arr2, k%, x As Boolean
 Dim rg1 As Range: Set rg1 = sh1.Range("a1").CurrentRegion.Columns(3)
 Dim rg2 As Range: Set rg2 = sh2.Range("a1").CurrentRegion.Columns(3)
 Dim i%
 On Error Resume Next
 For i = 2 To rg1.Rows.Count
 If Application.CountA(sh1.Cells(i, 1).Resize(, 3)) < 3 Then Exit Sub
 rg1.Cells(i).Offset(, 1) = "No"
 arr1 = Split(rg1.Cells(i), ","): arr2 = Split(rg2.Cells(i), ",")
   For k = LBound(arr1) To UBound(arr1)
    x = IsError(Application.Match(arr1(k), arr2, 0))
    If Not x And (rg1.Cells(i).Offset(0, -2) & _
     rg1.Cells(i).Offset(0, -1)) = (rg2.Cells(i).Offset(0, -2) & _
     rg2.Cells(i).Offset(0, -1)) Then
     rg1.Cells(i).Offset(, 1) = "Yes"
     Exit For
     End If
    Next
    Next
     On Error GoTo 0
End Sub