Yes—Excel for Microsoft 365 can use XLOOKUP to return the options for a dependent drop-down, but XLOOKUP does not create the drop-down itself. Put the lookup result in a helper cell, let it spill into a list, then point the second list’s Data Validation rule at that spill range; use FILTER in the helper formula if you need to remove blank cells.
What a dependent drop-down does
A dependent drop-down changes its choices based on a selection made elsewhere. For example, a person might choose a department in one cell and see only that department’s job titles in the next cell. The first list is the parent; the second is the dependent list. This is useful in forms, order sheets, inventory records, and other workbooks where a long list of unrelated choices would be difficult to navigate.
Excel’s Data Validation feature creates the selectable list. XLOOKUP can retrieve a matching range of options, while a dynamic-array formula can prepare that range for validation. These are separate parts of the solution: XLOOKUP finds the right choices, a helper formula displays them, and Data Validation presents them as a drop-down.
The example below uses three categories—Food, Drink, and Office—and their related items. It assumes the categories are arranged across a header row, with each category’s items beneath it. This layout makes it possible for XLOOKUP to return the column under the chosen category.
Set up the source data
On one worksheet, enter the category names in B1:D1 and their options below them in B2:D8. For example, the Food column might contain Fruit, Bread, and Rice; Drink might contain Water, Tea, and Coffee; Office might contain Pens, Paper, and Envelopes. Leave unused cells empty if one category has fewer items than another.
Use F2 for the parent selection, G2 for the dependent selection, and H2 as a helper cell. Keeping the helper on the same worksheet is convenient because Data Validation can then refer directly to its spill range. You can move the helper to another worksheet later, but a defined name is usually needed for a cross-sheet validation source.
This layout is deliberately simple. If your source data is already stored as a two-column list—one column for category and one for item—you can use FILTER to produce the dependent options instead. That structure is often easier to maintain when categories change frequently; the XLOOKUP example here is especially clear when each category has its own source column.
Build the parent and dependent lists
Select F2, then choose Data > Data Validation. In the Data Validation dialog, set Allow to List and enter =$B$1:$D$1 in Source. Confirm the rule. F2 now offers the three category headers. For a workbook with many categories, you could first create a separate compact range of category names rather than including unused header cells.
Next, place the helper formula in H2. With category headers in B1:D1, category items in B2:D8, and the selected category in F2, enter:
=FILTER(XLOOKUP(F2,$B$1:$D$1,$B$2:$D$8,""),XLOOKUP(F2,$B$1:$D$1,$B$2:$D$8,"")<>""," ")
The final argument above returns a space if no matching nonblank item is found; to have the helper return an empty string instead, use this version:
=FILTER(XLOOKUP(F2,$B$1:$D$1,$B$2:$D$8,""),XLOOKUP(F2,$B$1:$D$1,$B$2:$D$8,"")<>"","")
In this formula, XLOOKUP matches the value in F2 against the headers and returns the corresponding column from B2:D8. FILTER then removes empty cells from that column. If F2 contains Food, H2 spills the three nonblank choices Fruit, Bread, and Rice downward. The first version is not necessary for the normal setup; the second is the recommended formula because it returns a blank result if the category has no usable matches.
Now select G2 and choose Data > Data Validation. Set Allow to List and enter =$H$2# as the Source, then confirm. The # spill operator refers to all cells currently populated by the formula starting in H2, so the validation list expands or contracts with the selected category. If your Excel build does not accept the spill reference directly in the Source box, define a workbook name that refers to the helper spill and use that name as the source.
How XLOOKUP and FILTER work together
XLOOKUP normally returns the result corresponding to a match, but its return-array argument can include multiple rows or columns. Here, the lookup range is horizontal (B1:D1), while the return array is the larger block below it (B2:D8). Finding one header therefore returns the matching vertical column as an array. FILTER evaluates that array and keeps only cells that are not empty.
XLOOKUP uses exact matching by default, which is appropriate for category names. If the value in F2 does not match a header, the formula’s if-not-found argument supplies an empty string. FILTER’s final argument also prevents a no-results error when there are no matching entries to display. In a well-controlled workbook, the parent validation rule makes an unmatched category unlikely, but handling it in the formula makes the helper more robust.
FILTER preserves the order of the source items and does not remove duplicates. If the source column contains the same item twice, it will appear twice in the drop-down. Clean the source data if duplicates are not wanted. Also note that a cell containing a space is not truly blank, so the formula’s blank check will retain it; clear stray spaces in the source list.
Keep the lists reliable as the workbook grows
Spill formulas need room to display their results. Keep the cells below H2 clear, or Excel may show a #SPILL! error and the dependent list will not have a usable range. Do not type notes or other values into the helper’s spill area. You can place helper formulas in a hidden or clearly labeled area, but hiding the worksheet does not remove the need for the spill range to remain available.
When people change the parent after choosing a child, Excel does not automatically erase the old child selection. For example, someone may choose Food and then Fruit, change the parent to Office, and still see Fruit in G2 even though Fruit is no longer a valid Office choice. Data Validation helps prevent new invalid entries, but it does not retroactively clear an existing value. Users should reselect the child after changing the parent, or a workbook can use additional automation if automatic clearing is essential.
For a single dependent pair, the one-helper-cell setup is straightforward. For many rows, each row needs a helper formula tied to its own parent selection, and each dependent validation rule must point to the matching helper spill. Plan space for those helper results and test several rows; a single helper tied to F2 cannot independently serve many different parent selections at once.
When to use a different source layout
A wide layout with one column per category is easy to read, but it can become awkward when new categories are added or when categories have very different numbers of items. A normalized table has two fields instead: Category and Item, with one row for each valid pairing. For example, rows might contain Food–Fruit, Food–Bread, and Drink–Water.
With a normalized list, FILTER is usually the direct tool for returning items that match the selected category. XLOOKUP is not required to filter a table by category; it is most helpful when the category headers correspond to separate return columns, or when you need to look up one associated value. Choosing the source layout first can make the formulas simpler and reduce maintenance when the workbook changes.
You can also use named ranges for older or more constrained workbook designs. A common older pattern uses names for each category and INDIRECT in the validation source, but category names must be made compatible with Excel’s naming rules, and the approach can be harder to manage. A helper formula with a spill reference is generally easier to inspect in Microsoft 365.
Version and platform considerations
XLOOKUP and FILTER are available in Microsoft 365 and Excel 2021 or later. This article targets Excel for Microsoft 365 on Windows. Excel for Mac has the same core functions and Data Validation feature, though ribbon placement and dialog appearance can differ slightly. Excel for the web also supports many dynamic-array workflows, but some workbook editing controls and validation dialogs can vary; if a source field will not accept a spill reference, try a defined name or set up the rule in desktop Excel.
In older Excel versions that do not support XLOOKUP or FILTER, the formula shown here will not calculate. Use a compatible approach such as category-specific named ranges, or open the workbook in a version that supports these functions. A workbook containing dynamic-array formulas may display results in older versions, but the formulas may not be editable or recalculate as intended there.
Common problems and fixes
- The dependent list is empty: Check that the parent text exactly matches a header, including spaces, and that the corresponding source column has nonblank entries.
- Excel shows #SPILL!: Clear cells below the helper formula and check for merged cells or other content blocking its output.
- The Source box rejects the formula: Confirm the helper formula is on the same worksheet. If needed, create a defined name referring to the helper spill and use that name in Data Validation.
- The old child choice remains after changing the parent: This is normal; validation does not clear previous entries automatically. Select a new child value.
- The choices include duplicates or odd blank entries: Remove duplicate source values and clear cells that contain spaces instead of being truly empty.
Frequently Asked Questions
Can XLOOKUP create a drop-down list by itself?
No. XLOOKUP returns a value or array; Data Validation creates the interactive list. Put the lookup result in a helper cell and use its spill range as the validation source.
Does a dependent drop-down require INDIRECT?
No. In Microsoft 365, a helper formula that returns a spill range can avoid INDIRECT. INDIRECT is one alternative, particularly in older named-range designs, but it is not required for the XLOOKUP-and-FILTER method.
Why does my child drop-down not clear when I change the parent?
Data Validation controls choices when a value is entered, but it does not remove a value that was valid under the previous parent. Reselect the child, or use additional automation if the workbook must clear it automatically.
Can the source data be in an Excel table?
Yes. Tables are useful for maintaining source records, especially in a Category–Item layout. The key requirement is that the helper formula return the options as a spill range that the validation rule can reference.
Will this formula work in Excel 2019?
No, not as written. XLOOKUP and FILTER are available in Microsoft 365 and Excel 2021 or later, but not Excel 2019. Use a compatible named-range method or a newer Excel version.
Related
- Cell in Microsoft Excel: Meaning, Types, and How to Use It
- Count Numbers vs Count in Excel PivotTables
- Copy Paste in Excel: Keep Column Widths When Pasting
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUMIF Example Formula: Syntax, Uses, and Common FixesWith product names in A2:A7 and sales amounts in B2:B7, use =SUMIF(A2:A7,"Apples",B2:B7) to add the sales for Apples;…
- Excel COUNTIF Range Examples: Text, Numbers, and DatesUse Excel’s COUNTIF function to count cells in a range that match one condition: for example, =COUNTIF(A2:A20,"East") counts…
- Excel Formula for Date Range: Count Dates and MatchFor dates in A2:A100 and a start date in D2 and end date in E2, count dates in…
- COUNTIF Excel Blank? Count Empty Cells CorrectlyTo count blank cells in Excel, use =COUNTIF(A2:A20,""), replacing A2:A20 with your range. COUNTIF can also count cells…
- Concatenate Names in Excel With a Comma?To concatenate names in Excel with a comma and a space, use =TEXTJOIN(", ",TRUE,A2:A4) when the names are…
- AVERAGE vs AVERAGEIF in Excel: Which Should You Use?Use AVERAGE to find the mean of numeric values in a range; use AVERAGEIF to average only values…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.