Matching multiple cell values on a table

Hello,

I would like to know how this can be done with excel (if it can be done)? As you can see in the image, I have a group of values (three cells in this case) to find and match in a list of data table, and if the values are present (should be present in the same row), 1 for yes and 0 for no output in the following column. So basically instead of one value in a single cell, I want to search and match three values in three cells on a table. Thanks.


Note: * the values may not be present in adjacent cells on the table every time.


** Groups of values should be on the same row to give a positive output of 1.

http://imgur.com/JL17xci

Example.xls

Could you post a sample spreadsheet with the results you hope to see? You can click on "Go Advanced" and then use the paperclip icon to attach a file.

Does this work?

=IF(AND(COUNTIF($G$2:$J$6,A2),COUNTIF($G$2:$J$6,B2),COUNTIF($G$2:$J$6,C2)),1,0)

The only difference is that your last line should show as present because 3, 10, and b are all in the list of data.

Oh wait. I didn't remember that they all needed to be in the same row in the list of data.

I couldn't figure out a formula to do wat you want, but this code worked for me.

Sub test()
Dim LastRow As Long, endRow As Long
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Dim rng As Range, rng2 As Range
Set rng = Range("G2:J6")

For i = 2 To LastRow
    With rng
        endRow = rng.Rows.Count
        For r = 1 To endRow
            Set rng2 = .Rows(r).Find(what:=Cells(i, 1), Lookat:=xlWhole, MatchCase:=False)
            If rng2 Is Nothing Then
                GoTo NewRow
            Else: End If
            Set rng2 = .Rows(r).Find(what:=Cells(i, 2), Lookat:=xlWhole, MatchCase:=False)
            If rng2 Is Nothing Then
                GoTo NewRow
            Else: End If
            Set rng2 = .Rows(r).Find(what:=Cells(i, 3), Lookat:=xlWhole, MatchCase:=False)
            If rng2 Is Nothing Then
                GoTo NewRow
            Else: End If
            Cells(i, 5) = 1
NewRow:
        Next r
    End With
    
    If Not Cells(i, 5) = 1 Then
        Cells(i, 5) = 0
    End If
Next i
End Sub