Vlookup Issue

[jgrant1]

Hi,

I have been asked by work to create a spreadsheet where records are constantly changing as a result of a risk rating. Each month a new set of data is pasted into the master spreadsheet removing all data from the previous month. There are some constants that do not change month on month such as the client reference number. I have now been asked to link a comments box that allows users of the spreadsheet to add their comments against the relevant reference number. The problem here was that I needed to protect these comments (to create a rolling commentary) from being deleted when the new records were being copied in. After a lot of trying i used the below code to solve this issue:

 Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "AJ1:AJ1000"
Dim SavedVal As Variant

    On Error GoTo ws_exit
    Application.EnableEvents = False

    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
        With Target
        
            SavedVal = .Value2
            Application.Undo
            If SavedVal <> .Value2 Then
            
                .Value2 = SavedVal
            End If
        End With
    End If

ws_exit:
    Application.EnableEvents = True
End Sub

Now the challenge I face is linking these comments to the client reference number so that if a clients risk rating changes and they fall down the list the comment would automatically follow. I have toyed with the idea of a vlookup string but so far I have been unsuccessful in my attempts. Any ideas would be greatly appreciated.