Function Problems

How to Create a Dependent Dropdown List from VLOOKUP Results in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to create an Excel data validation dropdown that displays multiple corresponding values based on a primary selection, similar to a VLOOKUP returning multiple matches.

Product: Excel
Device & OS: not provided
Scenario: Setting up a dependent dropdown list across worksheets where selecting a specific value (e.g., '1' in cell K5) dynamically generates a specific subset of options (e.g., 'a' and 'b').
Observed behavior: Standard VLOOKUP only returns the first matching record. The user requires a multiple dependent dropdown list scenario to populate all matching results into the validation list.

Before you start

Before creating your dependent dropdown, ensure your source data is organized on a separate worksheet without blank rows, and reserve an empty column to serve as your dynamic helper range.

Solution 1Recommended

Create a Filtered Helper Range for Data Validation

Since VLOOKUP only returns a single value, using the FILTER function to create a dynamic helper range is the most efficient way to generate a multiple dependent dropdown list.

The standard VLOOKUP function cannot return an array of multiple matching values into a Data Validation list directly. Instead, you must extract the matching items into a helper column first, and then point your dropdown list to that column.

Set up the helper column

Select a blank cell on your helper worksheet. Enter a formula like `=FILTER(Sheet2!B:B, Sheet2!A:A=K5, "")`, replacing the ranges with your actual source data worksheet and primary selection cell.

Open Data Validation

Select the target cell where you want the dependent dropdown to appear (e.g., cell K6). Navigate to the Data tab on the Excel ribbon and click on Data Validation.

Configure the list source

In the Data Validation dialog box, select 'List' from the Allow dropdown menu.

Reference the spill range

In the Source field, select the first cell of your helper column and add a hash symbol (#) at the end (e.g., `=HelperSheet!A1#`). This ensures the dropdown captures all dynamic results.

Apply and test

Click OK. Change the value in your primary cell (K5) and verify that the dependent dropdown updates to show the correct matching multiple values.

Dynamic Array Support: Using the hash (#) operator requires an Excel version that supports Dynamic Arrays (Excel 365, Excel 2021, or WPS Spreadsheet). It automatically resizes your dropdown list based on the number of matches.

Solution 2

Use INDEX and MATCH for Older Excel Versions

If your spreadsheet software does not support the FILTER function, you can use a combination of array formulas and dynamic named ranges.

WPS Spreadsheet Solutions

Easily Create Dynamic Dropdowns with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array formulas like FILTER, making it incredibly easy to set up complex multiple dependent dropdown lists without relying on outdated legacy functions.

1. Extract Data: Open your file in WPS Spreadsheet and use the FILTER function in an empty column to extract matching data based on your primary cell.
2. Access Validation Settings: Highlight the cell meant for the dependent dropdown, navigate to the Data tab, and click the Validation button.
3. Apply List Source: Choose 'List' under Settings, and point the Source to your filtered helper range using the spill operator (#). Click OK.

Natively supports modern dynamic array functions for faster data extractionFully compatible with Microsoft Excel (.xlsx) formats and legacy formulasIntuitive Data Validation interface for seamless dropdown configurationFree and lightweight office suite for seamless data management

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I just put a VLOOKUP formula directly in the Data Validation Source?

Data Validation lists require an array or a range of cells as their source. VLOOKUP is designed to return only a single value (the first match it finds), so it cannot generate a list of multiple different matching values on its own.

How do I hide the helper column used for the dropdown?

You can place the helper column on a completely separate worksheet. Once your formulas are working, right-click the sheet tab at the bottom and select 'Hide'. The Data Validation will still read the hidden helper range perfectly.

What should I do if my dependent dropdown shows blank spaces?

Ensure that the 'Ignore blank' option is checked in your Data Validation settings. Additionally, verify that your helper column formula doesn't generate empty strings for non-matching rows, or use a dynamic spill range (#) to avoid referencing blank cells.

Excel & Data Problems