Find Multiple Matches & Dependent Drop Down List in Excel (Unsorted Tabular Data Set)

Dive into the world of dynamic dependent dropdown lists in Excel! Perfect for users who regularly work with unorganized datasets, this tutorial demonstrates how to create dropdown lists that adapt to your data changes. Ideal for those dealing with non-grouped, random data entries.

🔑 Key Features:
- Dynamic Dropdown Lists: Master the creation of dropdown lists that automatically update as new data is added or existing data is modified.
- Dealing with Multiple Data Instances: Learn to handle datasets where names or items are not grouped but scattered randomly.
- Excel Table Feature Integration: Leverage the power of Excel tables for expanding dropdown lists, ensuring new names or items reflect in your dropdowns seamlessly.
- Formula-Driven Approach: Explore complex formulas that make these dropdowns possible, ensuring a dynamic and responsive data management experience.
- Step-by-Step Guidance: The video walks you through each step with clear explanations, making it easy to understand and implement.
- Downloadable Workbook Template: Access a ready-to-use Excel workbook template to practice and implement the techniques learned.

This video covers a few topics:
1. How to find multiple matches - (basically an INDEX MATCH that finds all matches instead of the first one only)
2. How to fix table column referencing (absolute column referencing in tables)
3. How to use OFFSET to restrict the dependent drop-down list to non-empty members only (exclude empty cells)

We're going to learn all this, while creating a dependent drop-down list based on a messy random tabular data set.

★★ Links to related videos: ★★

🚩Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel

[HarveyA]

I so wish I found this channel years ago. I'm a pretty copmpetent excel (inc VBA) user but there is so much to learn here. I have never really got into INDEX, MATCH and OFFSET but I'm now a full convert. Thanks

[raghebabdalbaqi]

Thanks Laila, very beneficial video, I've similar lengthy table but with duplicated values, how can we get the second dependent list with unique values only?
Thank u so much for the useful tutorials.

[gopalakrishnarao]

I whole heatedly thank you for the pain, trouble taken for explaining and posting this video. On behalf of all my Excel friends/addicts/users/learners, I sincerely thank you. Again and Again I repeatedly saying that you are doing a great Job in the Excel field. Many, many are getting benefit in their official/ personal work by learning the tricks from your Excel videos. A Big ROYAL SALUTE to my EXCEL TEACHER/GURU.

[McKaySavage]

For those who find the formulas too complex to easily implement in your own projects (it's an excellent method though if you can figure it out), you can use a pivot table instead to create the aggregate. That is far easier to set up and simplifies the drop-down offset formula as well. The trick and limitation with using a pivot table though is that you do need remember to refresh the pivot table every time data changes. An added benefit is that the pivot table can sort as well. (but those who have Office365 insiders will know the new dynamic array formulas are game-changers for these scenarios as well)

[mathewdavies]

This is a massive help, however i was hoping you might be able to show how i would you go about doing this with a third and forth dependant drop down list?

[mirrrvelll]

Dear Leila,

Amazing video(s) from you! I got a salary raise recently by using your tips and tricks in excel, and combine them into my work!!

Very much appreciated!! Keep us informed!!

All the best!

[megankelley]

THANK YOU THANK YOU THANK YOU!!! I've spent the past two days looking for this exact

[Softwaretrain]

Hi leila, Thanks for your training.
You are really awsom but I think you could divide row by criteria part instead of dividing by itself and multiply by row.

[sandravukovic]

Hi Leila, nice solution! I had a similar task, but I solved it by putting the following into C4: IF(A4=$E$4;COUNTIF($B$4:B4);""), pulled it all the way down, and then INDEX/MATCHed a list of numbers (1, 2, 3...) with column C. But, the difference was, I never had a same record twice (in other words, each combination of A and B column occured only once). Your solution is great when this is not the case. Thanks for sharing!

[girishbhagwan]

Very Nice explanation and easy method to make layman understand Excell. Thankyou for making such videos it really gives amatures to learn what they need.
Keep up the Good Work Leila

[dennismoriarty]

Leila, THANK YOU!  While I didn't need the 2nd drop down box, the solution to pull in only those Apps by Division played well for a scenario at work.  This video was a MASSIVE help!

[nicktristani]

Hi Leila! Your videos are easy to digest and understand and allow anyone from a novice beginner like myself to someone well versed in excel. I was wondering how would you create a dependent drop down based on two criteria. For example, lets say you had another column next App in the table and it read Creator and that was the people who created the app. How would you go about listing division based on App AND Creator. Thanks in advance for your time!

[mattshelton]

Very Good Info. Love To See More. Keep the Videos Coming. I Have learned a lot from you.

[migueljesus]

Hi Leila, thanks for the video and for the explanation...
When trying to do the formula on my excel, when i copy the formula to the next column and when such cell does not have a indice reference (in case of your example it's the $F$7:F$7) it gives me error #NUM! ...
My question is, if you in the end of the video are deleting such reference (that according to my excel is a mandatory property of the AGGREGATE function), how does such function works if you are deleting such reference ?
Just wondering this cause on mine it works only if such reference exits... ohh and i am using Excel 2019 so for some reason i don't have some functions that i see sometimes on web such has FILTER, QUERY, etc.

Thanks in advance

[mohammadanwarmuashir]

This was much helpful. Thank you very much for posting wonderful videos in Excel.

[melaniedoiron]

This video is super helpful, however do you happen to have an example of showing how you would you go about doing this with a third and forth dependent drop down list? Example: Division, Category, Inventory Item and Price.

[mujeebuddinsarfaraz]

In addition to the excellent excel knowledge rbf Explanation is awesome

[kangsungho]

Your solutions are very creative and useful. I have been lookong for lectures like this. Thanks a lot.

[shaukatsamad]

Thx, for teaching us, I got knowledge thru you n youtube, the basic source I got from u n the others which are very brilliant n sharp minded personnel, since beginning when I need some help I just open yr page n get thru what I need....

[tonifrost]

What I was seeking for and perfectly explained. Step by step in oder to understand well how it works