Answered

Write Excel formula adds unwanted @ symbol

(0) Share

Report

Posted on by drkeek

Hi,

I'm trying to add this following formula to a particular cell in Excel:
=IFERROR(INDEX(B:B,MATCH(1,(B:B="%CurrentItem[1]%")*(G:G="SOME RANDOM TEXT"),0)),"FALSE")

The bot actually wrote formula as:
=IFERROR(INDEX(B:B,MATCH(1,(@B:B="%CurrentItem[1]%")*(@G:G="SOME RANDOM TEXT"),0)),"FALSE")

This changes the outcome of the formula, making it always return FALSE.
How can I fix this?

Categories:

Power Automate Desktop

All responses (3)

Answers (1)

Sort by

Verified answer

Community Power Pla... Microsoft Employee on at

Like
a
(2)

Report
Copy link

Link copied!

Are you using 'Write to Excel Worsheet'?
Instead, try using 'Send keys':
1. 'Focus windows', window class = XLMAIN (you have to focus your workbook first in order to send keys)
Inside your loop (since you're using CurrentItem):
2. 'Select cells in Excel worksheet' to define where you'll write the formula
3. 'Send keys' {F2}
4. 'Send keys' =IFERROR(INDEX(B:B,MATCH(1,(B:B="%CurrentItem[1]%")*(G:G="SOME RANDOM TEXT"),0)),"FALSE") {Return}
MichaelAnnis 5,727 Moderator on at

Like
a
(2)

Report
Copy link

Link copied!

@Anonymous is a great solution. Another would be to write your formulas to a variable, and then put the variable in the Write to Excel command, so there are no expression parameters messing with your text.
drkeek 35 on at

Like
a
(0)

Report
Copy link

Link copied!

thank you @Anonymous ! Your solution works a treat!

@MichaelAnnis I tried putting mine in variable but unfortunately didn't work for this one. I've no doubt it will work in some cases though.

I have a 3rd solution, though less elegant:
1. Have the formula in place as a template on top row (row 2 in this case as row 1 is header)
=IFERROR(INDEX(B:B,MATCH(1,(B:B=M2)*(G:G=N2),0)),"FALSE")
2. The formula now references the values in M2 and G2 instead
3. Copy paste values into relevant rows in column M and G
4. Copy paste formula from row 2 to relevant row