VBA Help - Lookup Match Between 2 Worksheets

[Web_Jim]

I have two workbooks (both with tens of thousands of rows). Customer_bu_breakdown and Master.

My "customer_bu_breakdown" summary sheet contains the information I want to match up.

• Column A is a customer ID column (which is the same as column F in my sheet1 worksheet).
• Column B on the Summary Sheet is a 'date from' column
• Column C on the Summary Sheet is a 'date to' column
• Column D on the Summary Sheet is 'business unit' which is what I want to return on the Master sheet.

Column G on the sheet1 worksheet (in Master) is a date/time field for "message sent".

What I'm looking to do is return the value in column D where "client ID" matches and "message sent" is on or after the "date from" AND "message sent" is on or before the "date to". (The summary sheet contains customer IDs multiple times which is why I'm not able to just do a lookup)

I came up with this formula as a starting point but because of the number of rows, it's incredibly slow to enter the formula in all of the fields, and then to paste special the values.

=IFERROR(INDEX('[customer_bu_breakdown.xlsx]Summary'!$D$2:$D$81579,MATCH(1,('[customer_bu_breakdown.xlsx]Summary'!$A$2:$A$81578=F7)*(DATEVALUE(TEXT(Sheet1!G7,"dd/mm/yyyy"))>=DATEVALUE(TEXT('[customer_bu_breakdown.xlsx]Summary'!$B$2:$B$81578,"dd/mm/yyyy")))*(DATEVALUE(TEXT(Sheet1!G7,"dd/mm/yyyy"))<=DATEVALUE(TEXT('[customer_bu_breakdown.xlsx]Summary'!$C$2:$C$81578,"dd/mm/yyyy"))),0)),"")

I thought a macro may help speed things up, and I've cobbled one together but it's not doing what it should and it's returning @ symbols in the formula (it's not yet paste-special-ing the values so I can see the formula)

=IFERROR(INDEX([customer_bu_breakdown.xlsx]Summary!$D$2:$D$81579,MATCH(1,(@[customer_bu_breakdown.xlsx]Summary!$A$2:$A$81578=Sheet1$F$7)*(DATEVALUE(TEXT(Sheet1!$G$7,"dd/mm/yyyy"))>=DATEVALUE(TEXT(@[customer_bu_breakdown.xlsx]Summary!$B$2:$B$81578,"dd/mm/yyyy")))*(DATEVALUE(TEXT(Sheet1!$G$7,"dd/mm/yyyy"))<=DATEVALUE(TEXT(@[customer_bu_breakdown.xlsx]Summary!$C$2:$C$81578,"dd/mm/yyyy"))),0)),"")

When I remove the @'s the formula works but with them in, it just returns a blank value. I feel like I'm close to a solution but can't seem to get it over the line, and I'm wondering if someone can help me either finish what I've started or advise me of a better way of achieving what I'm trying to achieve.

Sub BULookup()
    Dim LastRow As Long
    Dim SourceWorkbook As Workbook
    Dim SourceWorksheet As Worksheet
    Dim TargetWorkbook As Workbook
    Dim TargetWorksheet As Worksheet
    Dim i As Long
    
    ' Disable automatic calculation to improve performance
    Application.Calculation = xlManual
    
    ' Set the source workbook and worksheet
    Set SourceWorkbook = Workbooks("customer_bu_breakdown.xlsx")
    Set SourceWorksheet = SourceWorkbook.Sheets("Summary")
    
    ' Set the target workbook explicitly (change to your target workbook's name)
    Set TargetWorkbook = Workbooks("master.xlsx") ' Change to your target workbook's name
    
    ' Set the target worksheet explicitly (change "Sheet1" to your actual sheet name)
    Set TargetWorksheet = TargetWorkbook.Sheets("Sheet1")
    
    ' Find the last row with data in the target worksheet
    LastRow = TargetWorksheet.Cells(TargetWorksheet.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each row starting from row 7 in the target worksheet
    For i = 7 To LastRow
        ' Put your formula here
        TargetWorksheet.Cells(i, "K").Formula = "=IFERROR(INDEX('" & SourceWorkbook.Name & "'!$D$2:$D$81579,MATCH(1,('" & SourceWorkbook.Name & "'!$A$2:$A$81578=" & TargetWorksheet.Cells(i, "F").Address & ")*(DATEVALUE(TEXT(" & TargetWorksheet.Cells(i, "G").Address & ",""dd/mm/yyyy""))>=DATEVALUE(TEXT('" & SourceWorkbook.Name & "'!$B$2:$B$81578,""dd/mm/yyyy"")))*(DATEVALUE(TEXT(" & TargetWorksheet.Cells(i, "G").Address & ",""dd/mm/yyyy""))<=DATEVALUE(TEXT('" & SourceWorkbook.Name & "'!$C$2:$C$81578,""dd/mm/yyyy""))),0)), """")"
    Next i
    
    ' Calculate all the formulas in the target worksheet
    TargetWorksheet.Calculate
    
    ' Enable automatic calculation
    Application.Calculation = xlAutomatic
End Sub