Excel Data Validation List Based on a Lookup

Yes. In Microsoft Excel for Microsoft 365, make a lookup-driven dropdown by using FILTER in a helper cell, then point Data Validation to the resulting spill range through a defined name. This lets the choices change when the lookup value changes; the key detail is that Data Validation should refer to the helper output, rather than trying to place a lookup formula directly in the list’s Source box.

What a lookup-based validation list does

Data Validation can restrict a cell to a list of permitted entries. A lookup-based list takes that idea further: Excel uses a value elsewhere in the sheet—such as a department, product code, or selected category—to determine which entries appear in the dropdown.

For example, a form might ask for a software category and then offer only products assigned to that category. If the user selects Hardware, the next dropdown could show Mouse and Keyboard; if the user selects Software, it could show Excel and Word. The source list is therefore conditional, not a fixed set of values.

There are two related situations that are easy to confuse:

One result per lookup: A formula such as XLOOKUP returns one matching value. That can fill a cell, but it does not by itself create a dropdown of several choices.
Several permitted results: A formula such as FILTER returns all list entries matching a criterion. This is usually the right pattern for a dependent dropdown.

Set up the lookup data

Start with a tidy, two-column source table. On a sheet named Entry, create a table named Options with the headers Group and Choice. Include one row for every permitted choice:

Hardware — Mouse
Hardware — Keyboard
Software — Excel
Software — Word

Keep each group name consistent. A trailing space or a different spelling can prevent a match, and repeated group names are expected because each choice occupies its own row. Select the data and use Insert > Table; confirm that the table has headers. With a cell in the table selected, use Table Design > Table Name to set its name to Options.

Put the controlling selection in B2 on Entry—for example, the user chooses Software there. Reserve H2 for the helper formula and keep the cells below it clear so Excel has room to display the results. A dynamic-array formula cannot spill into occupied cells, and spill formulas should be placed outside an Excel table.

Build the dynamic list with FILTER

With the Options table containing Group and Choice columns and the selected group in Entry!B2, enter this formula in Entry!H2:

=FILTER(Options[Choice],Options[Group]=B2,"")

For the sample data, if B2 contains Software, the formula spills Excel and Word into H2 and H3. If B2 contains Hardware, it spills Mouse and Keyboard instead. The final argument returns an empty text result when there are no matches, rather than showing a calculation error.

FILTER is available in Microsoft 365 and Excel 2021 or later. The spill reference operator, written as a hash sign after the first cell, represents the entire changing result. It is useful here because the matching list might contain two choices today and five after the source table is expanded.

Connect the spill range to Data Validation

Data Validation’s List Source field is not the best place to enter the FILTER formula directly. Instead, create a defined name that points to the helper cell’s spill range, then use that name as the list source:

On Windows, select Formulas > Name Manager > New. Enter OptionsForSelection as the name and choose Workbook for the scope.
In Refers to, enter =Entry!$H$2#, then save the name. This reference means “the full spill range starting in H2 on Entry.”
Select the cell that should contain the dropdown. Choose Data > Data Validation.
On the Settings tab, set Allow to List and enter =OptionsForSelection in Source. Leave In-cell dropdown selected, then choose OK.

With the sample setup, the dropdown offers Excel and Word when Entry!B2 is Software. Change B2 to Hardware and the available choices change to Mouse and Keyboard. The defined name is important because a Data Validation list source cannot always accept a direct reference to a range on another worksheet; using a workbook-level name provides a clean reference to the helper spill.

If the name or helper formula refers to a different sheet, update the sheet reference accordingly. When a sheet name contains spaces, enclose it in apostrophes in the reference, as in ='Order Entry'!$H$2#.

Use a lookup value as an intermediate step

Sometimes the selection in the form is a code, while the source list is grouped by a readable category. You can look up the category first and use it to filter the choices in one formula. Suppose A2 contains a department code, the Departments table has Code and Group columns, and the Options table still has Group and Choice columns. With those tables and columns in place, enter this in the helper cell:

=FILTER(Options[Choice],Options[Group]=XLOOKUP(A2,Departments[Code],Departments[Group],""),"")

If A2 contains S and the matching row in Departments maps S to Software, the helper spills Excel and Word. The outer FILTER returns every choice in that group; XLOOKUP supplies the group name. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. If the code is not found, its fourth argument returns an empty string, so the formula does not display the usual not-found error.

This formula still needs the same defined-name and Data Validation steps: have the name refer to the helper cell’s spill range, then use that name in the list Source box. For a simple one-to-one lookup—such as returning one employee name for an employee ID—use a lookup formula in the result cell instead. A one-result formula is not a substitute for a list when users must choose among several valid entries.

Choose between dynamic arrays and older methods

For a new Microsoft 365 workbook, FILTER with a spill range is generally straightforward to maintain: the source remains in a normal table, new rows can be included as the table grows, and the formula returns all matches. XLOOKUP can be added when the form supplies a code that needs translating to a group.

Older workbooks may use named ranges for each category and INDIRECT to turn a category selection into a range name. That approach can work when categories are stable and carefully named, but it requires maintaining a separate range for each group. Names with spaces and changes to category labels also need attention. It is not necessary to create those names when FILTER can generate the matching list.

Excel 2019 and earlier do not have FILTER or the spill operator used in this example. In those versions, use a conventional helper range with formulas supported by that version, or maintain separate named ranges for each category. Excel for the web and Mac may present dialogs differently from Windows; the central design is the same, but if the Name Manager or validation setup is unavailable in a particular environment, create and verify the defined name in the Microsoft 365 desktop app.

Common problems and practical fixes

The dropdown is empty. Check that the controlling value exactly matches the Group entries, including spaces. Confirm that the helper formula returns results before changing Data Validation settings.
The formula shows a spill error. Clear cells below and beside the helper formula where results need to appear. Also make sure the formula is not inside an Excel table.
The name is not recognized. Check the spelling in both Name Manager and the Source field. For a list name, the Source entry should start with an equals sign: =OptionsForSelection.
Only the first result appears. Make sure the defined name refers to the spill range, for example =Entry!$H$2#, rather than only to =Entry!$H$2.
A choice remains after its controlling value changes. Changing the source list does not necessarily erase a value already entered in the dependent cell. Review the existing entry or clear it when the controlling selection changes.
The list does not include new source rows. Use an Excel table for the lookup data and check that the formula’s table and column names match the actual table headers.

Data Validation prevents many invalid selections made through the dropdown, but it should not be treated as a complete data-cleaning system. Users can paste values into cells, and existing entries may not become valid merely because the list changes. For important records, add a separate check—such as conditional formatting or a review formula—to flag values that no longer match the permitted choices.

Frequently Asked Questions

Can I put FILTER directly in the Data Validation Source box?
It is more reliable to calculate the matches in a helper cell, define a name referring to the helper spill range, and use that name as the Source. This separates the list-generation formula from the validation setting.

Do I need a helper cell for a dependent dropdown?
For the spill-based method described here, yes: the helper cell gives FILTER a place to return its results. You can put it on a support sheet and hide that sheet if appropriate, but keep the helper range available for Excel to calculate.

Will the dropdown update when I change the lookup value?
The choices are recalculated from the current lookup value. However, Excel does not automatically remove a value already entered in the dropdown cell if that value is no longer in the refreshed list.

Can one validation list work for many rows?
Yes, but each row needs a list tied to that row’s controlling value. A common design uses a helper output for each row and validation references that resolve to the corresponding output. Test copied validation carefully, because a single fixed helper reference would make every row show the same choices.

Why does Data Validation reject my worksheet reference?
List validation has restrictions on direct references to other sheets. A workbook-level defined name that points to the helper range is a standard way to make the list available to the validation rule.

Does this work in older Excel versions?
The exact FILTER-and-spill formula requires Microsoft 365 or Excel 2021 or later. Earlier releases need a different helper-formula or named-range design; they cannot use this dynamic-array formula as written.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.