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

 

[karl_learnvba]

Can anybody help with this query ?
Happy to revert to a VLOOKUP if makes it more accessible?
Thanks in advance.
Karl.

 

[Peter_SSs]

Does this do what you want?

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Sub Copy_WorkingNotes_Back_Into_Working_Dataset_Table_v2()
    Range("Working_Table[Notes]").Formula = "=XLOOKUP([@[Opportunity id]],Notes_Copy_Table[Opty_id],Notes_Copy_Table[Notes],"""")"
End Sub

 

[karl_learnvba]

Hi Peter,
Thank you v much for helping me w this query!
Unfortunately the above did not work.

I have added some more detail to see if that helps in figuring this out. Hope you can continue to work with me on this one!

Workbook is ThisWorkbook
Worksheet is called "Notes Temp"
Table is called "Notes_Copy_Table"
Table looks like this"

Worksheet is called "Working Dataset"
Table is called "Working_Dataset"
Table looks like this"

Outcome that I would like to obtain is to perform an xlookup to populate the notes in the "Working_Dataset" table with the notes in the "Notes_Copy_Table" using the Opportunity Id as the lookup value.

Thank you in advance,
Karl

 

[Fluff]

How about

Sub Copy_WorkingNotes_Back_Into_Working_Dataset_Table_v2()
    Range("Working_Dataset[Notes]").Formula = "=XLOOKUP([@[Opportunity id]],Notes_Copy_Table[Opty ID],Notes_Copy_Table[Notes],"""")"
End Sub

 

[karl_learnvba]

How about

Sub Copy_WorkingNotes_Back_Into_Working_Dataset_Table_v2()
Range("Working_Dataset[Notes]").Formula = "=XLOOKUP([@[Opportunity id]],Notes_Copy_Table[Opty ID],Notes_Copy_Table[Notes],"""")"
End Sub

Hello @Fluff ! Thank you for your help,... come on England on Saturday BTW - I am also a rugby fan, even though I live in USA these days.

This works! Thank you.

I have an additional question if you could help,.....

I was expecting just the result of xlookup to be in the cell (e.g. xxx) which I could then add to. However the formula is actually in the cell.

Is there a way to just obtain the value ?

Thank you,
Karl.

 

[Fluff]

Ok, how about

Sub Copy_WorkingNotes_Back_Into_Working_Dataset_Table_v2()
   With Range("Working_Dataset[Notes]")
      .Formula = "=XLOOKUP([@[Opportunity id]],Notes_Copy_Table[Opty ID],Notes_Copy_Table[Notes],"""")"
      .Value = .Value
   End With
End Sub

 

[karl_learnvba]

Ok, how about

Sub Copy_WorkingNotes_Back_Into_Working_Dataset_Table_v2()
With Range("Working_Dataset[Notes]")
.Formula = "=XLOOKUP([@[Opportunity id]],Notes_Copy_Table[Opty ID],Notes_Copy_Table[Notes],"""")"
.Value = .Value
End With
End Sub

@Fluff
Thank you - This is exactly what I needed.
I appreciate your help.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[lifezhwy]

This is great! Is there an easy way to run the macro either for the selected table row only, or only for the rows where the lookup value is not blank?

What I am trying to do is that if I select PPM ID (from a drop-down list) the code runs and populates Level 0 (leveraging xLookup), but if I leave PPM ID blank, the code doesn't run for that row and doesn't overwrite Level ) column.

Here is the code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("Tbl_Input_DB[PPM ID]")) Is Nothing Then

Call Copy_v1

End If

End Sub


Sub Copy_v1()

With Range("Tbl_Input_DB[Level 0]")

.Formula = "=XLOOKUP([@[PPM ID]],Q_BOW_LCM[PPM ID],Q_BOW_LCM[Pillar],"""")"

.Value = .Value

End With

Many thanks in advance!