'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Fix Excel 365 MATCH Formula Not Showing Table Fields
Question details
The user is unable to see table field autocomplete suggestions when typing a MATCH formula in Excel 365, a feature that previously worked in Excel 2016.
- Product
- Excel 365
- Device & OS
- not provided
- Scenario
- Writing a MATCH formula that uses structured references to point to specific fields within a formatted data table.
- Observed behavior
- Excel 365 fails to display the dropdown list of table fields during formula entry, preventing easy selection of structured references.
Ensure that your data is explicitly formatted as an Excel Table (via Insert > Table) rather than just a standard cell range, as structured references only work with official tables.
Remove Periods from the Table Name
Renaming the affected table to remove any periods restores the autocomplete suggestions for table fields in Excel 365.
Excel 365 occasionally struggles to parse structured references if the table name contains a period (.). This parsing bug causes the formula autocomplete feature to fail when typing functions like MATCH. Removing the period from the table's name allows Excel to recognize the structured reference correctly.
Click on any cell inside the affected table to reveal the Table Tools on the ribbon.
Navigate to the 'Table Design' (or simply 'Design') tab at the top of the Excel window.
Look at the 'Properties' group on the far left side of the ribbon to find the 'Table Name' input box.
Delete any periods (.) from the current name. You can replace them with underscores (_) if you need to separate words, then press Enter to save.
Type your MATCH formula again. When you type the new table name followed by a bracket [, the table fields will now appear as suggestions.
Use WPS Spreadsheet for Flawless Formula and Table Referencing
WPS Spreadsheet fully supports structured table references and the MATCH function. It offers reliable formula autocomplete, so you can seamlessly reference table fields without worrying about version-specific glitches found in Excel 365.
- 1. Format as Table: Open your dataset in WPS Spreadsheet, select the data range, and press Ctrl+T to create a formatted table.
- 2. Name Your Table: Navigate to the Table Tools tab and assign a clean name to your table to keep your formulas organized.
- 3. Enter the Formula: Click the cell where you want the result, type =MATCH(, and select your lookup value.
- 4. Use Structured References: Type the table name followed by a bracket [. WPS will instantly display a dropdown of all available table fields for easy selection.
Frequently Asked Questions
What characters are restricted in Excel table names?
Excel table names cannot contain spaces, most special characters, or start with a number. While periods are technically allowed by the system, they often cause parsing bugs with formula suggestions in newer versions like Excel 365.
Why doesn't Excel autocomplete formulas for me at all?
This usually happens if the 'Formula AutoComplete' feature is disabled. Go to File > Options > Formulas, and ensure that the 'Formula AutoComplete' checkbox is checked under the 'Working with formulas' section.
How does the MATCH function work with structured references?
When using a structured reference, you type the table name followed by a bracket, such as Table1[Column1]. The MATCH function then scans this specific column array to find and return the relative numerical position of your lookup value.
