Vlookup From another workbook

[Sunil7598]

Hi All,

I just need a code for vlookup from another workbook, I tried a lot but's not working properly. I even tried to recorded macro but workbook name changes everytime.

Actual requirement is Lookup value is in P column of Loan contract & lookup range SFDC_report.Sheets("SFDC Data").Range("B2:AC" & lr).
Lookup should start from Col AO to BI & lr and Col Index Num is 4 to 28 of sfdc report

Set Loan_contract = ActiveWorkbook
Set SFDC_report = Workbooks.Open("X:\WK13\ES_SFDC_data_03_05_2023.xlsx")
Dim rng As Range, lr1 As Long, lcol, lcol1 As Long, lr As Long
Dim i, j, k As Long
lr1 = Loan_contract.Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
lcol1 = Loan_contract.Sheets("Data").Cells(1, Columns.Count).End(xlToLeft).Column

lr = SFDC_report.Sheets("SFDC Data").Cells(Rows.Count, "B").End(xlUp).Row
lcol = SFDC_report.Sheets("SFDC Data").Cells(1, Columns.Count).End(xlToLeft).Column
Set rng = SFDC_report.Sheets("SFDC Data").Range("B2:AC18079")

Loan_contract.Sheets("Data").Activate
k = 4

For j = 41 To lcol1

For i = 2 To lr1

Dim lookupvalue As String
lookupvalue = Loan_contract.Sheets("Data").Cells(i, 16).Value

Loan_contract.Sheets("Data").Cells(i, j).Value = Application.IfError(Application.WorksheetFunction.VLookup(lookupvalue, rng, k, False), "#NA")

Next i
k = k + 1
Next j

thank you for your fast reply. Let me know if need any additional information