Function Problems

How to Create an Excel Drop-Down List from FILTER or VLOOKUP Results

Camila Milosovich Oct 13, 2026 868 views

Question details

The user wants to create a dependent drop-down list that displays all matching results for a specific criteria, rather than just returning one value.

How to Create an Excel Drop-Down List from FILTER or VLOOKUP Results

Product: Excel
Device & OS: not provided
Scenario: Setting up a dynamic data validation list where selecting a region automatically populates a drop-down list with all messengers corresponding to that region.
Observed behavior: When attempting to use VLOOKUP, only the first matching messenger is returned, preventing the user from seeing or selecting all valid options.

Before you start

Ensure your dataset is organized in a clear tabular format and designate a blank helper column to temporarily hold the dynamic array results.

Solution 1Recommended

Use the FILTER Function and Spilled Range for Data Validation

Since VLOOKUP is limited to returning a single value, the FILTER function is the recommended method to extract all matching items for a dependent drop-down list.

The VLOOKUP function stops searching as soon as it finds the first match. To retrieve all matching items (for example, all messengers in a specific region), you need a dynamic array function like FILTER.

By outputting the FILTER results into a helper range, you can reference this spilled array directly in your Data Validation settings.

Create the helper formula

In an empty cell (e.g., H2), enter the FILTER function to extract the matching data. For example, use =FILTER(B2:B100,A2:A100=E2) where B contains messengers, A contains regions, and E2 is your selected region.

Open Data Validation

Select the cell where you want the dependent drop-down list to appear. Go to the 'Data' tab on the ribbon and click 'Data Validation'.

Set validation to List

In the Data Validation dialog box, under the 'Allow' drop-down, choose 'List'.

Reference the spilled range

In the 'Source' box, type the cell reference of your helper formula followed by a hashtag, like =H2#, and click 'OK'.

Use the FILTER Function and Spilled Range for Data Validation

Using Named Ranges: If your spreadsheet software restricts spilled array references (like =H2#) in the Data Validation dialog, create a Named Range pointing to =H2# in the Name Manager, and use that Name in the Source box instead.

Efficient Spreadsheet Management

Easily Create Dynamic Drop-Down Lists in WPS Spreadsheet

WPS Office fully supports dynamic arrays and advanced lookup functions, allowing you to easily build dependent drop-down lists with the FILTER function without expensive subscriptions.

1. Open your data in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the source data.
2. Apply the FILTER function: Type your =FILTER() formula in a blank helper column to automatically generate the list of matching items based on your criteria.
3. Access Data Validation: Click the cell for your drop-down, navigate to the Data tab, and select 'Validation'.
4. Configure the list source: Select 'List' from the criteria options and input the spilled range reference (e.g., =H2#) to finish setting up your dynamic drop-down.

Fully compatible with Microsoft Excel (.xlsx) file formatsSupports dynamic array functions including FILTER, XLOOKUP, and UNIQUEFree and lightweight alternative to Microsoft ExcelIntuitive Data Validation interface for streamlined data entry

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't VLOOKUP work for creating drop-down lists with multiple options?

VLOOKUP is designed to search for a specific key and return only the first matching value it finds in the dataset. Because it cannot return an array of all matches, it is unsuitable for populating a drop-down list where multiple valid options exist.

What does the hashtag (#) mean in a cell reference like =H2#?

The hashtag denotes a spilled range reference. When you use a dynamic array function like FILTER, the results automatically spill into adjacent empty cells. Adding the hashtag tells the software to include the entire dynamic output starting from cell H2.

Can I hide the helper column used for the FILTER results?

Yes, you can safely hide the helper column to keep your main worksheet looking clean. Simply right-click the column letter at the top and select 'Hide'. Your Data Validation drop-down list will continue to function normally.

What if my version of Excel doesn't support the FILTER function?

If you are using an older version that lacks dynamic array functions, you will need to use a combination of INDEX, MATCH, and COUNTIF formulas along with helper columns to extract multiple matches, or upgrade to a modern suite like WPS Office that supports these functions for free.

Excel & Data Problems