How to Automatically Apply VLOOKUP to New Microsoft Forms Responses in Excel
Question details
The user wants to auto-apply a lookup formula to retrieve user information for new rows added to an Excel sheet via Microsoft Forms and Power Automate, looking to replicate Google Sheets' ArrayFormula behavior.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Collecting data via Microsoft Forms using Power Automate and automatically performing a lookup on related user information whenever a new entry is populated.
- Observed behavior
- Needs a dynamic solution to extend the lookup formula to newly inserted rows automatically, bypassing the need to drag the formula down manually or rely on Google Sheets ArrayFormula.
Ensure your destination data range is formatted as a formal Excel Table, and that you have a separate reference list or table containing the data you wish to look up (such as user emails and names).
Use Structured References in an Excel Table
Formatting your destination data as an Excel Table creates calculated columns, which automatically copy formulas down to new rows added by Power Automate.
Unlike Google Sheets, Microsoft Excel uses Excel Tables to handle dynamic row additions seamlessly. When data is formatted as a Table, adding a formula to one cell in a new column turns it into a 'calculated column' that automatically applies to all existing and future rows.
Select your raw Microsoft Forms response data range in Excel. Press the keyboard shortcut Ctrl+T (or go to Insert > Table), ensure 'My table has headers' is checked, and click OK.
Click on the cell immediately to the right of your Table's last header and type a new column name, such as 'User Email'. Press Enter. The Table will automatically expand to include this new column.
In the first empty cell under your new header, type your VLOOKUP or XLOOKUP formula using the Table's structured references (e.g., clicking on the 'Name' cell in that row rather than typing A2).
Press Enter. Excel will automatically apply the formula to the entire column. When Power Automate pushes a new Microsoft Forms response to the bottom of the table, the formula will instantly evaluate for the new row.
Perform the Lookup Directly in Power Automate
Bypass Excel formulas entirely by retrieving the necessary lookup data within the Power Automate flow before writing the row to Excel.
Master Data Tables with WPS Spreadsheet
Managing dynamic tables and automated lookups is incredibly straightforward with WPS Spreadsheet. It fully supports Excel's Table features and structured references, ensuring your formulas seamlessly expand as new data comes in.
- 1. Open Your Data: Launch WPS Spreadsheet and open your existing data file.
- 2. Insert a Table: Highlight your dataset and press Ctrl+L, or navigate to the Insert tab and click 'Table' to convert the range into a formal table.
- 3. Write Your Formula: Add a new column header, then type your VLOOKUP formula in the first cell of that column.
- 4. Watch it Auto-Fill: Hit Enter. WPS Spreadsheet automatically turns it into a calculated column, applying it instantly to all rows and automatically adapting to any newly pasted entries.
Frequently Asked Questions
Why doesn't ArrayFormula work in Excel like it does in Google Sheets?
Microsoft Excel handles dynamic arrays differently than Google Sheets. While modern Excel has dynamic array functions (often called 'Spill' ranges), these spill arrays are not supported inside formal Excel Tables. Using an Excel Table's calculated column is the native Microsoft equivalent to ArrayFormula for structured data.
Will calculating VLOOKUP on every new row slow down my Excel file?
Using an Excel Table with calculated columns is very efficient because the formula only executes on populated rows. However, if your Microsoft Form generates tens of thousands of responses over time, fetching the data in Power Automate instead might improve your workbook's performance.
Can I use XLOOKUP instead of VLOOKUP in an Excel Table?
Yes, XLOOKUP is fully supported inside Excel Tables. It is often recommended over VLOOKUP because it is faster, allows right-to-left lookups, and adapts better if column orders change.
What should I do if my table formula stops automatically filling down?
If the formula stops auto-filling, your AutoCorrect settings may have been altered. Go to File > Options > Proofing > AutoCorrect Options, navigate to the 'AutoFormat As You Type' tab, and ensure 'Fill formulas in tables to create calculated columns' is checked.
