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

karl_learnvba

New Member
Joined
Oct 10, 2019
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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.


VBA Code:
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

karl_learnvba

New Member
Joined
Oct 10, 2019
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Can anybody help with this query ?
Happy to revert to a VLOOKUP if makes it more accessible?
Thanks in advance.
Karl.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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’)

VBA Code:
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

New Member
Joined
Oct 10, 2019
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Hi Peter,
Thank you v much for helping me w this query!
Unfortunately the above did not work.

1614171548055.png


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"

1614170928754.png


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

1614171222487.png


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
 

Attachments

  • 1614171173079.png
    1614171173079.png
    3.7 KB · Views: 1

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
VBA Code:
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

New Member
Joined
Oct 10, 2019
Messages
10
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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.

1614175203156.png


Is there a way to just obtain the value ?

Thank you,
Karl.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Ok, how about
VBA Code:
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
 
Solution

karl_learnvba

New Member
Joined
Oct 10, 2019
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,125
Messages
5,628,851
Members
416,344
Latest member
Maug2004

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top