by Wyn Hopkins

If you've ever needed row-by-row dependent dropdowns in Excel — where each row can have its own independent selection across multiple levels — you've probably found most solutions clunky, formula-heavy, or fragile. 

This one is different. And honestly? It might be the simplest approach you've ever seen. 

🤔You might like to check out our Online Professional Excel Productivity Course

Is this solution any good?

Wyn walks through a clever technique using a XLOOKUP : XLOOKUP range trick that makes multi-level dependent dropdowns extremely easy to set up. No complex array formulas, no VBA — just a smart combination of named ranges and XLOOKUP.

Open File

In this tutorial, Wyn covers:

• How to set up your lookup table (and why it should be sorted — plus a workaround if it isn't)
• Using Named Ranges to get around data validation's table range limitation
• The XLOOKUP : XLOOKUP technique — using two XLOOKUPs separated by a colon to return a dynamic range from first match to last match
• Building Level 1, Level 2, and Level 3 dropdowns, each feeding off the previous selection
• Copying the data validation down so every row works independently

Step 1 Create a Table and Name it t and label the columns 1,2, 3 to keep it simple later 

Step 2 Create A Named Range called L1List =t[1]See the 4 steps in the image

Step 3 Add a Data Validation Drop Down list to cell A1 of Sheet1 and reference L1List 

Step 4 Create a Named Range called L2List  (repeat the clicks in step 2)

=XLOOKUP(Sheet1!A1,t[1],t[2])   

:  XLOOKUP(Sheet1!A1,t[1],t[2],,,-1) 

Step 5 Add a Data Validation Drop Down list to cell B1 of Sheet1 and reference L2List

Step 6 Create a Named Range called L3List  

=XLOOKUP(Sheet1!A1&Sheet1!B1,  t[1]&t[2],  t[3])  
:  XLOOKUP(Sheet1!A1&Sheet1!B1,  t[1]&t[2],  t[3],,,-1) 

Step 7 

Add a Data Validation Drop Down list to cell C1 of Sheet1 and reference L3List 

You can now copy the 3 cells down multiple rows and the validation will work! 

The video also shows adding conditional formatting to flag invalid selections when a parent dropdown changes — so stale child values turn red automatically 

Plus it demonstrates what to do if your source table can't be guaranteed to stay sorted — using SORTBY to create a pre-sorted helper range, and CHOOSECOLS to split it into separate named ranges 

Finally the video shows a quick heads-up about what breaks if you cut and paste the setup to another sheet — and how to fix the named range references in a few clicks 

Whether you're building data entry tools, project trackers, or any workbook where users need to make cascading selections row by row, this technique is clean, maintainable, and works beautifully in up-to-date versions of Excel. 

Want to build smarter, more intuitive Excel tools for your business? Get in touch with the Access Analytic team — we'd love to help.  And check out our Online Professional Excel Productivity Course for more gems of information from Wyn.