How to Autofill Data from a Drop-Down List in Older Excel Versions
Question details
The user needs to autofill data based on a drop-down list selection in older Excel versions where modern dynamic-array functions are unavailable.
- Product
- Microsoft Excel (2010, 2013)
- Device & OS
- not provided
- Scenario
- Creating a workbook with a drop-down selection that automatically populates corresponding data, intended for users with legacy versions of Excel.
- Observed behavior
- Modern functions like UNIQUE and FILTER result in calculation errors on older versions, requiring the use of traditional formulas, Power Query, or VBA to achieve the desired autofill behavior.
Verify the exact version of Excel your target audience is using (e.g., Excel 2010 or 2013), as this determines whether you must use traditional workaround formulas or if you can rely on newer dynamic features.
Use INDEX and MATCH with Helper Columns
This traditional formula method works universally across all older versions of Excel to retrieve and autofill matching records.
Since dynamic arrays like FILTER are not supported in legacy Excel versions, you can use a combination of INDEX, MATCH, and a helper column to pull multiple records that match your drop-down selection.
Insert a new column next to your source data to generate unique IDs for duplicate entries using a COUNTIF formula (e.g., =A2&COUNTIF($A$2:A2, A2)).
Navigate to the Data tab, select Data Validation, and set up your drop-down menu referencing your main criteria list.
In your target cells, write an INDEX and MATCH formula that references the helper column to pull in the corresponding rows based on the drop-down selection.
Drag the formula down and across your target range to automatically populate the remaining data whenever the drop-down value changes.
Extract Multiple Rows Using Power Query
Ideal for scenarios where a single drop-down selection needs to return multiple rows and columns, providing a more maintainable setup.
Automate Data Retrieval with a VBA Macro
A programmable approach that instantly populates the target range without relying on complex formulas in the cells.
Upgrade to WPS Office for Modern Excel Functions
Instead of struggling with complex workarounds in Excel 2010 or 2013, switch to WPS Office. It's a free, lightweight suite that fully supports modern dynamic array functions like FILTER and UNIQUE, saving you time while maintaining flawless compatibility with Microsoft Excel formats.
- 1. Download and Install: Get WPS Office for free from the official website and install it on your device.
- 2. Open Your Workbook: Open your existing .xlsx file directly in WPS Spreadsheet without any formatting loss.
- 3. Use Modern Formulas: Apply dynamic array functions like FILTER to instantly autofill data based on drop-down selections.
Frequently Asked Questions
Why do my FILTER and UNIQUE formulas return an error in Excel 2013?
Excel 2010 and 2013 do not support dynamic array functions. If a workbook containing these modern functions is opened in older versions, they will return a #NAME? or _xlfn prefix error. You must use legacy functions or alternative software like WPS Office.
Can I use XLOOKUP to autofill drop-down data in Excel 2010?
No, XLOOKUP was introduced in much newer versions of Microsoft Office. For Excel 2010, you must use traditional lookup functions like VLOOKUP or a combination of INDEX and MATCH.
Does the Power Query method update instantly when I change the drop-down?
No, unlike standard cell formulas, Power Query results do not update in real-time. You must manually refresh the data table by right-clicking it and selecting 'Refresh' after changing the drop-down selection.
