VBA XLOOKUP from a table to another table based on column headers

[karl_learnvba]

Hello Experts, I am having a problem with both the syntax and also the code. Thanks in advance for your help.

Here's what I am trying to do:

1. I have 2 tables in different worksheets within the same workbook:
   1. One Table is called "Notes Copy Table"
   2. A 2nd Table is called "Working Table"
2. Within "Notes Copy Table" I have 2 variables.
   1. "Opty_id" - is a numerical identifier
   2. "Notes" - is a text
3. Within "Working Table" I would like to populate a column called "Notes" using an XLOOKUP function which:
   1. References the Opportunity ID within a column called "Opportunity id"
   2. Matches the "Opportunity id" to the "Opty_id" in the other table
   3. Returns the associated "Notes" and populates them within this table
   4. Additionally if the Opportunity ID was not found, then would return a blank rather than an error

Here is what I was developing but struggling,..... Thank you for your help.

Sub Copy_WorkingNotes_Back_Into_Working_Dataset_Table()

    Dim wb As Workbook, ws As Worksheet, ws1 As Worksheet
    Dim Lookup_Value As Range, LookupMatch_Value As Range, Result_Value As Range, rng As Range
    
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Working Dataset")               ' Table in this worksheet is also called "Working_Dataset"
    Set ws1 = wb.Worksheets("Notes Temp")                   ' Table in this worksheet is called "Notes_Copy_Table"
    
    Set Lookup_Value = "Working_Dataset[Opportunity id]"    ' Col Header is called Opportunity id within the table
    Set LookupMatch_Value = "Notes_Copy_Table[Opty ID]"     ' Col Header is called Opty ID within the table
    Set Result_Value = "Notes_Copy_Table[Notes]"            ' Col Header is called Notes within the table
    Set rng = "Working_Dataset[Notes]"                      ' Col Header is called Notes within the table
    
  
    rng.Value = Application.WorksheetFunction.XLOOKUP(Lookup_Value, LookupMatch_Value, Result_Value)

   
End Sub

 

[Fluff]

Please start a new thread for this question. Thanks