cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GKS
Level: Powered On

Inserting Excel formula via Flow and Referencing Current Cell

I need to use an index - match formula in my excel sheet.

 

=INDEX(B:B,MATCH(I2,G:G,0))

 

I can pass the formula in within the add row excel connector, but the Row Number does not increment, it stays as I2 for all rows.

 

I tried switching to RC notation, but it did not appear to persist in excel online.

 

My flow first deletes all rows and then inserts new rows, so the formula needs to be inserted via the flow.

 

Is there a way to reference the current cell within the Match function that will increment properly?  

 

 

Thanks!

3 REPLIES 3
Community Support Team
Community Support Team

Re: Inserting Excel formula via Flow and Referencing Current Cell

Hi @GKS ,

 

You could use add() function to set the Row number.

Use variables to get all the Row numbers, then configure max() function to get the maximum value.

After pulsing 1 to the maximum value, add Row Number of the new row.

64.PNG65.PNG66.PNG

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
GKS
Level: Powered On

Re: Inserting Excel formula via Flow and Referencing Current Cell

Thanks that sounds like it would work.

I was able to solve this by reconfiguring my formula to not rely on relative cell references, so I replaced I2 in

 

=INDEX(B:B,MATCH(I2,G:G,0))

 

with

 

INDEX(I:I,ROW()),G:G,0)

 

Making the final formula

 

=INDEX(B:B,MATCH(INDEX(I:I,ROW()),G:G,0))


So it references the correct cell using INDEX and ROW instead of a relative cell reference, which does not increment without a lot of customization.

Community Support Team
Community Support Team

Re: Inserting Excel formula via Flow and Referencing Current Cell

Hi @GKS ,

 

Great! In fact, using Excel formula in Flow is difficult to achieve its effect.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (3,843)