Power Automate

Answered

How to vlookup data from 2 excel file in Pad?

(1) Share

Report

Posted on by Sanket1

357

Hi Team,

I have 2 excel file which contains almost 10k records.

EmpData - Excel and

IncidentExcel
__________________________________________________________________

IncidentExcel has bellow columns

Number

Caller

Employee number

State

Assignment group

Assigned to

Employee number

Short description

Description

Category

Priority

Impact

Urgency

Opened

Updated

Updated by

SLA due

Resolved

Resolved by

Resolution code

Resolution notes

Resolve time

Made SLA

Location

Domain

Closed

Closed by

Child Incidents

Business resolve time

Additional comments

Work notes

Updates

Time worked

Subcategory

Service

Reassignment count

Domain

On hold reason

User ID

Adding new columns in every time Incident excel - those columns are

ITLT, ITSLT, BU, Department, Work Area, Office City, Email ID.

- Emp excel has this column data

Employee Id

Full Name

ITLT

IT SLT

Business Unit

Department

Work Area

Office City

Email Id

Status

Now i want write this data including columns (

ITLT, ITSLT, BU, Department, Work Area, Office City, Email ID.)
in IncidentExcel file by using email id

Thank you so much in advance

Categories:

Power Automate Desktop

I have the same question (0)

All responses (12)

Answers (2)

Sort by

VishnuReddy1997 2,666 Super User 2026 Season 1 on at

Like (2)

Report
Copy link

Link copied!
Hi @Sanket1 ,

I have provided few sample methods how to can achieve Vlook UP in PAD.

Method 1:
You try with PAD action as shown below.

Code:
Excel.Attach DocumentName: $'''VL.xlsx''' Instance=> ExcelInstance Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: $'''Sheet2''' Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: $'''=VLOOKUP(A2,Sheet1!A:B,2,FALSE)''' Column: $'''B''' Row: 2 Excel.CopyCellsFromExcel.CopyCell Instance: ExcelInstance StartColumn: $'''B''' StartRow: 2 Excel.GetFirstFreeColumnRow Instance: ExcelInstance FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow Excel.SelectCellsFromExcel.SelectCells Instance: ExcelInstance StartColumn: $'''B''' StartRow: 2 EndColumn: $'''B''' EndRow: FirstFreeRow - 1 MouseAndKeyboard.SendKeys.FocusAndSendKeysByInstanceOrHandle WindowInstance: ExcelInstance TextToSend: $'''{Control}({V})''' DelayBetweenKeystrokes: 10 SendTextAsHardwareKeys: False

Method 2:
You can try with VBscript as shown below.

VBscript Code:
Const xlUp = -4162 Dim columnToConvert Const sourceExcelFile = "%Mouser_BOM_Files[0]%" Const destinationExcelFile = "%Output_Files[0].FullName%" Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.DisplayAlerts = True Set objSourceWorkbook = objExcel.Workbooks.Open(sourceExcelFile) Set objDestinationWorkbook = objExcel.Workbooks.Open(destinationExcelFile) Set objSourceWorksheet = objSourceWorkbook.Sheets(1) Set objDestinationWorksheet = objDestinationWorkbook.Sheets(1) sourceLastRow = objSourceWorksheet.Cells(objSourceWorksheet.Rows.Count, "A").End(xlUp).Row destinationLastRow = objDestinationWorksheet.Cells(objDestinationWorksheet.Rows.Count, "B").End(xlUp).Row For i = 2 To sourceLastRow sourceValue = objSourceWorksheet.Cells(i, 1).Value sourceKValue = objSourceWorksheet.Cells(i, 11).Value If Not IsEmpty(sourceKValue) And IsNumeric(sourceKValue) Then For j = 2 To destinationLastRow destinationValue = objDestinationWorksheet.Cells(j, 2).Value If sourceValue = destinationValue Then If sourceKValue = 0 Then objDestinationWorksheet.Cells(j, 6).Value = "" objDestinationWorksheet.Cells(j, 7).Value = objSourceWorksheet.Cells(i, 11).Value objDestinationWorksheet.Cells(j, 8).Value = objSourceWorksheet.Cells(i, 13).Value objDestinationWorksheet.Cells(j, 9).Value = objSourceWorksheet.Cells(i, 14).Value Else objDestinationWorksheet.Cells(j, 6).Value = objSourceWorksheet.Cells(i, 9).Value objDestinationWorksheet.Cells(j, 7).Value = objSourceWorksheet.Cells(i, 11).Value objDestinationWorksheet.Cells(j, 8).Value = objSourceWorksheet.Cells(i, 13).Value objDestinationWorksheet.Cells(j, 9).Value = objSourceWorksheet.Cells(i, 14).Value End If Exit For ' Exit the loop once a match is found in destination End If Next End If Next ' Specify the column you want to convert to General format (for example, column A) columnToConvert = "F" ' Convert the column to General format objDestinationWorksheet.Columns(columnToConvert).NumberFormat = "General" objSourceWorkbook.Close False objDestinationWorkbook.Close True objExcel.Quit Set objSourceWorksheet = Nothing Set objDestinationWorksheet = Nothing Set objSourceWorkbook = Nothing Set objDestinationWorkbook = Nothing Set objExcel = Nothing

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards
Vishnu Reddy

Was this reply helpful? Yes No
Sanket1 357 on at

Like (1)

Report
Copy link

Link copied!

I have 2 different excels so
how can I do?

Was this reply helpful? Yes No
VishnuReddy1997 2,666 Super User 2026 Season 1 on at

Like (2)

Report
Copy link

Link copied!

Hi @Sanket1 ,

In Method 2 I have done the same. You can see in the VBscript there is 2 excel files named as Source excel file and destination excel file.

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards
Vishnu Reddy

Was this reply helpful? Yes No
Sanket1 357 on at

Like (1)

Report
Copy link

Link copied!

Could you please vb script base on my requirement with columns name?
Apologies I did not get as much from vb script

Was this reply helpful? Yes No
VishnuReddy1997 2,666 Super User 2026 Season 1 on at

Like (2)

Report
Copy link

Link copied!

Can you provide BOTH Excel files and can you explain full deatils if BOTH are matching ,what and all values it should pick from source file and write it in destination file. If it contains senstive data send it as private message.

Was this reply helpful? Yes No
VishnuReddy1997 2,666 Super User 2026 Season 1 on at

Like (2)

Report
Copy link

Link copied!

Hi @Sanket1 ,

Can you let me know which column it should compare between 2 excels.

Regards,
Vishnu Reddy

Was this reply helpful? Yes No
Sanket1 357 on at

Like (1)

Report
Copy link

Link copied!

Empdata Email Id And incident excel Email

Was this reply helpful? Yes No
VishnuReddy1997 2,666 Super User 2026 Season 1 on at

Like (2)

Report
Copy link

Link copied!
Hi @Sanket1 ,

Please find the VBscript to do you above scenario.
Dim objExcel, objWorkbookSrc, objWorkbookDest, objSheetSrc, objSheetDest Dim lastRowSrc, lastRowDest, i, j, srcValue, destValue ' Create an instance of Excel application Set objExcel = CreateObject("Excel.Application") ' Open the source and destination workbooks Set objWorkbooksrc=objExcel.Workbooks.Open("C:\Users\Desktop\Power Automate Desktop\Practice\VLOOKUP\EMP DATA.xlsx") Set objWorkbookDest = objExcel.Workbooks.Open("C:\Users\Desktop\Power Automate Desktop\Practice\VLOOKUP\EMP.xlsx") ' Get the first worksheet in each workbook (modify as necessary) Set objSheetsrc=objWorkbookSrc.Sheets(1) Set objSheetDest = objWorkbookDest.Sheets(1) ' Find the last row with data in the source and destination sheets lastRowsrc=objSheetSrc.Cells(objSheetSrc.Rows.Count, 3).End(-4162).Row ' Column C in source lastRowDest = objSheetDest.Cells(objSheetDest.Rows.Count, 9).End(-4162).Row ' Column I in destination ' Loop through each row in the source sheet For i = 1 To lastRowSrc srcValue = objSheetSrc.Cells(i, 3).Value ' Value in column C of source sheet ' Loop through each row in the destination sheet to find a match For j = 1 To lastRowDest destValue = objSheetDest.Cells(j, 9).Value ' Value in column I of destination sheet ' If a match is found, copy the data If srcValue = destValue Then objSheetSrc.Cells(i, 42).Value = objSheetDest.Cells(j, 3).Value ' Copy column C to AP objSheetSrc.Cells(i, 43).Value = objSheetDest.Cells(j, 4).Value ' Copy column D to AQ objSheetSrc.Cells(i, 44).Value = objSheetDest.Cells(j, 5).Value ' Copy column E to AR objSheetSrc.Cells(i, 45).Value = objSheetDest.Cells(j, 6).Value ' Copy column F to AS objSheetSrc.Cells(i, 46).Value = objSheetDest.Cells(j, 7).Value ' Copy column G to AT objSheetSrc.Cells(i, 47).Value = objSheetDest.Cells(j, 8).Value ' Copy column H to AU objSheetSrc.Cells(i, 48).Value = objSheetDest.Cells(j, 9).Value ' Copy column I to AV objSheetSrc.Cells(i, 49).Value = objSheetDest.Cells(j, 10).Value ' Copy column J to AW End If Next Next ' Save the source workbook objWorkbookSrc.Save ' Close the workbooks objWorkbookSrc.Close False objWorkbookDest.Close False ' Quit the Excel application objExcel.Quit ' Clean up Set objSheetsrc=Nothing Set objSheetDest = Nothing Set objWorkbooksrc=Nothing Set objWorkbookDest = Nothing Set objExcel = Nothing

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards
Vishnu Reddy

Was this reply helpful? Yes No
Sanket1 357 on at

Like (1)

Report
Copy link

Link copied!

Hi @VishnuReddy1997

I am trying to run above script but its running so long time ,

emp data contain 22k and incident data contain 22k records
is this reason for running long time?

Was this reply helpful? Yes No
VishnuReddy1997 2,666 Super User 2026 Season 1 on at

Like (2)

Report
Copy link

Link copied!
Hi @Sanket1 ,

If there is a larger data it will take time with VBscript.
In that case You need to try with Python Code.
Please find the Python code for that . Try running it with Powershell script action or Python Script action.
It will generate a new Output as a Output.
import pandas as pd # Load the Excel files df1 = pd.read_excel(r"C:\Users\OneDrive\Desktop\Power Automate Desktop\Practice\VLOOKUP\EMP DATA.xlsx") df2 = pd.read_excel(r"C:\Users\Desktop\Power Automate Desktop\Practice\VLOOKUP\EMP.xlsx") # Define the columns to copy columns_to_copy = ['ITLT', 'IT SLT', 'Business Unit', 'Department', 'Work Area', 'Office City', 'Email Id', 'Status'] # Iterate over each row in df1 for index, row in df1.iterrows(): email = row['Email'] # Change to the correct column name if needed # Find the match in df2 match = df2[df2['Email Id'] == email] # If a match is found, update the corresponding columns in df1 if not match.empty: for col in columns_to_copy: df1.at[index, col] = match.iloc[0][col] # Save the updated df1 to a new Excel file df1.to_excel(r"C:\Users\Desktop\VloopkupOutput.xlsx", index=False) print("Data transfer complete!")

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards
Vishnu Reddy

Was this reply helpful? Yes No