Others

How to Get a Unique Email from an Excel Lookup Table in Power Automate

Ayan Masood Oct 22, 2026 868 views

Question details

The user needs to dynamically retrieve a unique email address from an Excel lookup table in SharePoint using Power Automate based on a Microsoft Forms response, and use it in a Send an email action.

How to Get a Unique Email from an Excel Lookup Table in Power Automate

Product: Microsoft Power Automate, Microsoft Excel
Device & OS: not provided
Scenario: Automating email sending based on Microsoft Forms responses by looking up user details in an Excel spreadsheet.
Observed behavior: The goal is to automatically fetch and insert the correct email address into the 'To' field without requiring the form respondent to type it manually.

Before you start

Ensure your Excel file is stored in SharePoint or OneDrive for Business, data is formatted as a Table, and it contains a unique identifier (like a User ID) alongside the corresponding email column.

Solution 1Recommended

Retrieve the Email Address Using Excel Online (Business) Actions

Use the 'Get a row' or 'List rows present in a table' action to fetch the corresponding email address from your Excel table and insert it dynamically.

By utilizing a unique identifier captured in your Microsoft Form, you can search your Excel lookup table directly within Power Automate. Using the specific expression syntax ensures the flow extracts the email correctly, even if it is placed inside a loop.

Add an Excel retrieve action

After your Microsoft Forms trigger, add the 'Get a row' or 'List rows present in a table' action from the Excel Online (Business) connector.

Configure the lookup parameters

Select your SharePoint site, Document Library, File, and Table. Specify the Key Column (e.g., User ID) and provide the Key Value from your Microsoft Forms dynamic content.

Add the Send an email action

Insert the 'Send an email (V2)' action into your flow where the email needs to be sent.

Insert the dynamic email expression

Click on the 'To' field. If your flow placed the action inside an 'Apply to each' loop, open the Expression tab and enter items('Apply_to_each')?['User Email'] (adjust the column name to match your table), then click OK.

Retrieve the Email Address Using Excel Online (Business) Actions

Account Permissions: Verify that your Power Automate connection account has the necessary read permissions to access the SharePoint file where the Excel lookup table is stored.

Free Microsoft Office alternative

Manage Your Excel Lookup Tables Effectively with WPS Office

While Power Automate requires Microsoft's cloud ecosystem for workflow execution, managing the actual spreadsheet data, lookup tables, and formatting can be done entirely in WPS Office. WPS Office is a highly compatible, free, and lightweight alternative to Microsoft Office, perfect for structuring your automation data.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the .xlsx file you intend to use for your lookup table.
2. Format data as a Table: Highlight your lookup data range and press Ctrl+T (or use the Insert menu) to format it as a Table, which is strictly required by Power Automate.
3. Define clear headers: Ensure your columns have distinct headers, such as 'User ID' and 'User Email', to make mapping easier in your flow.
4. Save to cloud storage: Save the optimized .xlsx file directly to your connected SharePoint or OneDrive folder.

Fully compatible with Microsoft Excel (.xlsx) formats, ensuring seamless integration with cloud storage used by Power Automate.Lightweight design for quick editing of lookup tables and large datasets.Familiar interface makes transitioning from Microsoft Office effortless.Built-in tools for data validation and unique key management to prevent flow errors.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Power Automate put my 'Send an email' action inside an 'Apply to each' loop?

When you use actions like 'List rows present in a table', Power Automate returns an array of results. Because there could potentially be multiple rows matching your query, it automatically adds an 'Apply to each' loop to process each row, even if you know only one match exists.

What is the correct expression to extract the email in an Apply to each loop?

You can use the expression items('Apply_to_each')?['User Email'] in the dynamic content expression box. Make sure to replace 'User Email' with the exact name of the column header in your Excel table.

Why does the 'Get a row' action fail to find my Excel table?

Ensure that your data range in Excel is explicitly formatted as a Table (Insert > Table), and that the file is saved in a supported cloud location like SharePoint or OneDrive. Power Automate cannot read standard cell ranges that are not formatted as tables.

Office Quick Solutions