Hi - i'm running into a simmilar issue as this. Have you had any success?
I honestly don't completely remember this particular issue. My instincts are that I tried Offset(), but that evaluates constantly, so supremely slows Excel performance.
I believe the solution that worked involved using a Filter block. Filter has greatly improved performance in all my flows, and I wish I had learned about it way earlier. Filter the table for Location = "Location 1." This will leave you with one row with that location's details and accessing the email from there is easy.
Best Practice
After escalating a related issue through the Microsoft Support world, I was told that the solution for how to add formulas dynamically into Excel sheets is, don't do it. Basically, I was trained away from the approach described above using Flow to add formulas to Excel cells. The Best Practices approach is to put all your logic in Excel or all your logic into Flow, but don't mix; I still do, of course, but, in dire situations where I don't have the time to research the right way, I cheat, but carefully.
On Cheating (referenced from previous line)
If you put formulas into your Excel sheet, you are going to want to make the calculated values static. The way I have done this is to, at the end of the flow, grab each row and over-write it with itself. Sounds useless, right? Well, what happens is, Flow reads the value of the Excel cell and not the underlying formula so, when it writes back to the cell what it read, it over-writes the formula with the static value.
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
Learn to digitize and optimize business processes and connect all your applications to share data in real time.