VBA: How to match multiple criteria inside an array?

The code below does what you describe. Please try it.

Option Explicit

Sub FindMatches()
    ' 130
    
    Dim rngPrimary_Key      As Range
    Dim rngForeign_Key      As Range
    Dim Primary_Key         As Variant          ' array
    Dim Foreign_Key         As Variant          ' array
    Dim v                   As Variant          ' cell value
    Dim R                   As Long             ' loop ounter: Rows
    Dim m                   As Variant          ' Match result
    
    ' you must specify the tab on which a range is set
    With ThisWorkbook.Worksheets(1)
        Set rngPrimary_Key = .Range(.Cells(2, "A"), _
                                    .Cells(.Rows.Count, "A").End(xlUp))
        Set rngForeign_Key = .Range(.Cells(2, "G"), _
                                    .Cells(.Rows.Count, "G").End(xlUp))
        Primary_Key = rngPrimary_Key.Resize(, 2).Value
        Foreign_Key = rngForeign_Key.Resize(, 2).Value
    End With

    For R = LBound(Primary_Key, 1) To UBound(Primary_Key, 1)
        v = Primary_Key(R, 1)
        m = Application.Match(v, rngForeign_Key, 0)
        If IsError(m) Then
            Debug.Print v & " = No Match",
        Else
            Debug.Print v & " = Match",
            v = Primary_Key(R, 2)
            m = Application.Match(v, rngForeign_Key.Offset(0, 1), 0)
            Debug.Print v & " = " & IIf(IsError(m), "No ", "") & "Match"
        End If
        Debug.Print
    Next R
End Sub

I reversed your code's original logic to match your description of it. The above code loops through all primary keys to find a match among the foreign keys. If a match is found, it will check if the value in column B, next to the matching primary key in column A exists in column H (1 column to the right of column G).

As for syntax, I added Option Explicit at the top, named all variables and defined their data type. This will help you gain and keep ownership of the code. I learned that, apparently, one doesn't have to specify a worksheet and have VBA default to Worksheets(1). If this is really true - meaning if your code really worked - this would be typical Microsoft style "simplification". True, you may not need to specify a worksheet but in exchange you get to conflate workbook and worksheet, disabling your learning. I liked the previous logic whereby one specifies a cell by its workbook, its worksheet and is coordinates on that sheet. My code uses that logic.

On the design front, a lot in this code is presumed. For example, there is an underlying presumption that the length of column A = length of column B, and column H isn't longer than column G. If, in fact, all columns are of either equal or unequal length one would have used different syntax. For the second check, columns B and H aren't specified. They are presumed to be next to columns A and G which are specified by name. If you need the freedom to place those columns elsewhere more declarations will be needed.