web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

Welcome to the Power Platform Communities
Season of Giving Solutions is Here!
Explore Copilot Studio Forums, Blogs & Galleries!
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Blogs / Power Automate Community Blog / How to Apply VLOOKUP in Pow...

How to Apply VLOOKUP in Power Automate Desktop

Views (473)
VishnuReddy1997 Profile Picture VishnuReddy1997 2,656 Super User 2025 Season 2
How to Apply VLOOKUP in Power Automate Desktop

Introduction:

Applying VLOOKUP in Power Automate Desktop can significantly enhance your data processing capabilities by automating the lookup of values across different datasets. This is particularly useful for tasks that involve comparing and retrieving data from Excel sheets, making your workflows more efficient and reducing manual effort.

Prerequisites:
  • Power Automate Desktop installed: Ensure you have the latest version of Power Automate Desktop installed on your computer.
  • Excel file with data: Prepare an Excel file with the data you want to use for the VLOOKUP.
  • Basic understanding of VLOOKUP: Familiarity with the VLOOKUP function in Excel will help you understand and implement the steps more effectively.

Step-by-Step Guide

Method 1:

1.Launch Power Automate Desktop:
  • Open Power Automate Desktop and create a new flow.

2. Attach to Excel Instance:
  • Use the Launch Excel action to open your Excel files.
  • Use the Attach to running Excel action if the file is already open.
3. Set Active Worksheet:
  • Use the Set active worksheet action to select the sheet where you want to apply the VLOOKUP.
4. Write the VLOOKUP Formula:
  • Use the Write to Excel worksheet action to enter the VLOOKUP formula in the desired cell. For example:
  • = VLOOKUP(B6,'[GML.xlsx]Product-View'!$A:$K,6,0)
  • Adjust the formula according to your data range and requirements.



6. Copy the Formula:
  • Use the Copy/Paste Range action to copy the formula to the rest of the cells in the column.
 


7. Convert Formulas to Values:
  • Use the Send Keys action again to paste the values (e.g., using the shortcut Alt + E + S + V).


8. Save and Close the Excel File:
  • Use the Save Excel action to save your changes.
  • Use the Close Excel action to close the file.

Sample Code Snapshot:




Method 2:

Using Vbscript.

Dim objExcelBOM, objWbkBOM, objExcelArrow, objWbkArrow
Dim i, lastRow, j
Dim Count, cell
Dim FirstCol, SecondCol, ThirdCol, FourthCol
'Dome Output Template
Set objExcelBOM = CreateObject("Excel.Application")
Set objWbkBOM = objExcelBOM.Workbooks.Open("%Output_Files[1].FullName%")
Set objExcelArrow = CreateObject("Excel.Application")
Set objWbkArrow = objExcelArrow.Workbooks.Open("%Arrow_BOM_Files[0].FullName%")
objExcelBOM.Visible = True
objExcelArrow.Visible = True
Set objWkshtBOM = objWbkBOM.Sheets("Sheet1")
Set objWkshtArrow = objWbkArrow.Sheets("Best Buying Options")

lastRowBOM = objWkshtBOM.Cells(objWkshtBOM.Rows.Count, "B").End(-4162).Row
lastRowArrow = objWkshtArrow.Cells(objWkshtArrow.Rows.Count, "B").End(-4162).Row
Count = 2
For i = 2 To lastRowBOM
    For j = 2 To lastRowArrow
         If objWkshtArrow.Range("B" & i).Value = objWkshtBOM.Range("B" & j).Value Then
             If objWkshtArrow.Range("F" & i).Value = "-" Or IsEmpty(objWkshtArrow.Range("F" & i).Value) Then
                objWkshtBOM.Range("P" & j).Value = ""
                objWkshtBOM.Range("Q" & j).Value = objWkshtArrow.Range("F" & i).Value
                objWkshtBOM.Range("R" & j).Value = objWkshtArrow.Range("I" & i).Value
                objWkshtBOM.Range("S" & j).Value = objWkshtArrow.Range("Q" & i).Value
             ElseIf IsNumeric(objWkshtArrow.Range("F" & i).Value) Then
                objWkshtBOM.Range("P" & j).Value = objWkshtArrow.Range("M" & i).Value
                objWkshtBOM.Range("Q" & j).Value = objWkshtArrow.Range("F" & i).Value
                objWkshtBOM.Range("R" & j).Value = objWkshtArrow.Range("I" & i).Value
                objWkshtBOM.Range("S" & j).Value = objWkshtArrow.Range("Q" & i).Value
             End If
         End If
     Next
Next

' Save and close the workbooks
objWbkBOM.Save
objWbkArrow.Save

' Close the workbooks
objWbkBOM.Close
objWbkArrow.Close

' Quit the Excel applications
objExcelBOM.Quit
objExcelArrow.Quit

' Clean up the objects
Set objWkshtBOM = Nothing
Set objWkshtArrow = Nothing
Set objWbkBOM = Nothing
Set objWbkArrow = Nothing
Set objExcelBOM = Nothing
Set objExcelArrow = Nothing


Method 3:
Using Python Script.

import pandas as pd

# Load the Excel files
df1 = pd.read_excel(r"C:\Users\Desktop\Power Automate Desktop\Projects\BOM Costing\Phyton Output\Output.xlsx")
df2= pd.read_excel(r"C:\Users\Desktop\Power Automate Desktop\Projects\BOM Costing\DLM Folder\Material with Latest Purchase Price.XLSX")

# Iterate over each row in df1
for index, row in df1.iterrows():
    dlm_part_no = row['DLM Part Number']
    
    # Find matches in df2 based on 'Material' column
    match = df2.loc[df2['Material'] == dlm_part_no, 'Price in USD']
    
    # Check if there is at least one match
    if not match.empty:
        # Take the first match (if there are multiple, or you can handle them differently)
        price = match.iloc[0]
        # Update the 'DLM Price' column in df1
        df1.at[index, 'DLM Price'] = price

# Save the updated df1 to a new Excel file
df1.to_excel(r"C:\Users\Desktop\Power Automate Desktop\Projects\BOM Costing\Phyton Output\Output1.xlsx",index=False)


Conclusion:
By following the steps outlined in this blog post, you can effectively apply VLOOKUP in Power Automate Desktop to automate data retrieval and comparison tasks. This not only enhances your workflow efficiency but also minimizes manual errors, making your data processing more reliable and streamlined.























                                                                                                                                                                             
code snippet widget

Comments