Power Automate - Form to Excel to Email using vlookup to collect fields

[jamieannemiller]

I'm fairly new to Power Automate.

I am collecting a registration through forms and using a flow to move the data into Excel.  I am collecting name, email address, and date of training.  I am transferring this information in a flow to Excel.  From there, there are two vlookups to bring in the time and URL of the training.  I then want to email the individual the date, time, and URL of the training.  Everything works except the two vlookup fields going into the email - they come in blank.  I even tried to put in a 30 second delay between the data entry into the Excel file and the email.  The email is based off of the Excel file fields, not the form. 

Any ideas?  Or is there a better way to match up the fields to send confirmation emails?  I'm not married to using Excel.

[Jronash]

Can you post your flow?

This might or might not have anything to do with your situation, but your Excel data will not refresh once you have fetched it - so if you fetch the data at the beginning of your flow and then send an email at the end, that data will not have refreshed.  Even if it has changed in the meantime, even if you have built in a delay.  You'll have to fetch it again.

[jamieannemiller]

Here it is.  Is there a better way to post it?  Again, I'm new to Power Automate.  Thank you!

[Jronash]

Yes, the issue is that Excel is adding the dates in the vlookup fields AFTER your "Add a row into a table" action.  In order to include those values in an email, you need to retrieve fresh data.

Use a "Get a row" action to get data from the row you just entered.  You can add this action after your "Add a row into a table" action (I don't think you'll need the Delay).  Then make sure your email is using data from the "Get a row" action, not the "Add a row" action.

[jamieannemiller]

Thank you.  Does Key Column and Key Value determine which row I choose?  If so, I want the latest row so I can send an email confirmation based on that row.  How can I determine that or can this not be done this way?

[Jronash]

Yes, Key Column and Key Value determine the row that is returned.  Do you have a column in your table that will contain a unique value (like an ID column or a timestamp)?  If so, use that.  If not, you'll need to return all of the rows instead of just one and then use the last() expression to access the last row.  That's a bit more complex.

[jamieannemiller]

I do have a timestamp.  How could I use the last row entered based on time? 

[Jronash · Accepted solution]

Another way of explaining the Key/Value in the Get row action is that it will search your table, and return the first row it encounters where the Key (the column) matches the value that you supply.

So if you know the timestamp of the row you want to return, select that column in your "Get row" action as the Key, and put the timestamp in the Value.