cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Zulqarnain97
Frequent Visitor

Perform lookup Function in excel through power automate.

How can I Perform lookup Function in excel through power automate?

Please guide me with some Steps and I will be thankful to you.

1 ACCEPTED SOLUTION

Accepted Solutions

I would recommend using Office Scripts, specifically Office Scripts with Power Automate. This is the 'Run Script' action on the Excel connector. With Office Scripts you can use the 'Run Script' action to execute javascript against a workbook. Here's a quick sample script that takes the sheet, range you want the formula to be set in, and the formula as parameters. It then sets the formula and reads and returns that value back to Power Automate. So you can pass in any formula you like, not just vlookup.

function main(workbook: ExcelScript.Workbook, sheetName: string, formulaRangeAddress: string, formula: string) {
  let sheet = workbook.getWorksheet(sheetName);
  let formulaRange = sheet.getRange(formulaRangeAddress);
  formulaRange.setFormula(formula);
  return formulaRange.getValue();
}

 

View solution in original post

13 REPLIES 13
GeoffRen
Microsoft
Microsoft

Can you explain with an example what you mean by 'lookup Function'?

Actucally i want to utilize the vlookup function on power Automate 
fort function reference please click the link :https://download.microsoft.com/download/9/b/4/9b49c8c5-d7a9-45b1-b8b6-52067e9970a8/AF101984660_en-us... 

I would recommend using Office Scripts, specifically Office Scripts with Power Automate. This is the 'Run Script' action on the Excel connector. With Office Scripts you can use the 'Run Script' action to execute javascript against a workbook. Here's a quick sample script that takes the sheet, range you want the formula to be set in, and the formula as parameters. It then sets the formula and reads and returns that value back to Power Automate. So you can pass in any formula you like, not just vlookup.

function main(workbook: ExcelScript.Workbook, sheetName: string, formulaRangeAddress: string, formula: string) {
  let sheet = workbook.getWorksheet(sheetName);
  let formulaRange = sheet.getRange(formulaRangeAddress);
  formulaRange.setFormula(formula);
  return formulaRange.getValue();
}

 

Hi, 

Can you share an example on how the above script would be used

123213123
Helper III
Helper III

@GeoffRen hello, can you share example how to use it? 

Can you share what you have tried so far? Have you looked at the linked documentation and tried to run the script from Excel to test it? I made some small changes that will let you test it from within Excel with a very small example below:

 

function main(workbook: ExcelScript.Workbook, sheetName: string, formulaRangeAddress: string, formula: string) {
	sheetName = "Sheet1";
	formulaRangeAddress = "A1";
	formula = "=A2+A3";

	let sheet = workbook.getWorksheet(sheetName);
	let formulaRange = sheet.getRange(formulaRangeAddress);
	formulaRange.setFormula(formula);

	console.log(formulaRange.getValue());

	return formulaRange.getValue();
}

 

GeoffRen_0-1659977879100.png

 

VLOOKUP is working now, but it doesn't when i try to add IFERROR expression:

formula = "=IFERROR(VLOOKUP(D2,SHEET1!A:G,2,FALSE),"no")";

Should i leave below 3 options blank?

123213123_0-1659980481824.png

 

GeoffRen
Microsoft
Microsoft

Can you share what you mean by it isn't working? Does the script work when you run from directly in Excel? Can you also try escaping the quotations, ie "=IFERROR(VLOOKUP(D2,SHEET1!A:G,2,FALSE),\"no\")"

123213123
Helper III
Helper III

it works now when i run directly in Excel by adding the \ \ as per your instructions.
thank you!

123213123
Helper III
Helper III

is it possible to combine two vlookup formulas in one script? the return index number will only change.

GeoffRen
Microsoft
Microsoft

Definitely. I'm no expert on Excel formulas so I'm not sure how to do that using just formulas, but you can just add another couple lines for adding another formula in the script. The following returns an array containing the results of both formulas. You can extend it to use as many formulas as you want. 

 

 

function main(workbook: ExcelScript.Workbook, sheetName: string, formulaRangeAddress: string, formula: string, otherFormula: string) {
	let sheet = workbook.getWorksheet(sheetName);
	let formulaRange = sheet.getRange(formulaRangeAddress);
	formulaRange.setFormula(formula);

    let ret = [];
    ret.push(formulaRange.getValue());

	formulaRange.setFormula(otherFormula);
    ret.push(formulaRange.getValue());

	return ret;
}

 

hi GeoffRen,

 

Why is this visible in my flow, do i need to enter the formula again?

 

123213123_0-1660118997205.png

 

GeoffRen
Microsoft
Microsoft

If you mean the sheetName, formulaRangeAddress, and formula fields, it's because those are defined as parameters in the script. Whatever values you put in those fields will be passed directly to the script as a parameter. So if you put the formula in this field, you don't need to hardcode it in the script. The biggest advantage here is that you can use the script in different Flows using different formulas for each Flow.

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (3,290)