I don't believe you can do what you want. There is no way (that I know of) to detect only pastes.
The closest is to use
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
But, this means it will fire every time any change on the page is made.
So, as a work around, what you can do is paste into Col Q and then manually run a macro (which you can assign to a button if easier).
Option Explicit
Sub MatchThePairs()
'You can edit this top bit
'The name of column you are pasting into
Dim pastedCol As String
pastedCol = "Q" 'UPDATE ME IF NEEDED
'The name of the look up column
Dim lookupCol As String
lookupCol = "Z" 'UPDATE ME IF NEEDED
'The name of the look to show results
Dim resultCol As String
resultCol = "AA" 'UPDATE ME IF NEEDED
'Do you want to clear the results first ?
Dim clearResults As Boolean
clearResults = True 'CHANGE ME TO True OR False
'What is the row of the header (if you have one)
Dim rowHeader As Integer
rowHeader = 1 ' set to 0 if no header
'What is the name of the results column
Dim resultsColHeader As String
resultsColHeader = "ResultsCol" ' Change me to what ever
'what is the first row (do not include the headings)
Dim row As Integer
row = 2 'AS PER THE SCEEN SHOT, I STARTED ON ROW 2
' **** hopefully you won't need to edit anything below this
If clearResults Then
Range(resultCol & ":" & resultCol).Cells.Clear
If rowHeader > 0 Then
Range(resultCol & rowHeader).Value = resultsColHeader
End If
End If
Do While (Range(pastedCol & row).Value <> "")
If Range(pastedCol & row).Value = Range(lookupCol & row).Value Then
'yipee, a match
Range(resultCol & row).Value = Range(lookupCol & row).Value
End If
row = row + 1
Loop
End Sub
Before
After
worksheet change event
and let us know where you get stuck