Find Multiple Matches in Excel & Create a Dependent Drop Down List

Based on an Unsorted Excel Table

Complex dependent Excel drop down list

The aim of this tutorial is to create a dependent drop-down list that’s based on a messy Excel Table.

What do I mean by an unsorted Excel table?

The categories for the first drop-down are not in a tabular format and they are not grouped together. You can see an example of the data set below. In a previous tutorial I showed you how you can extract unique items for a data validation drop-down list that was based on a table which had multiple instances of each category.

In this tutorial I will take this one step further and create a dependent drop down list based on the same data set.

Great thing is: to get there we need to overcome a set of challenges.

excel find many matchesFirst: How to Find Many Matches

To get to our dependent data validation list, we need to be able to extract all records for each category (in the example, all apps belonging to each division). My approach is to use the INDEX together with AGGREGATE function in order to come up with a CSE (Control Shift Enter) free version of the formula.

Second: Absolute Column Table Referencing

The second challenge is to fix the column referencing in the table so that it’s fixed and doesn’t move as we drag the formula

Third: Create a Drop-down List that Excludes Empty Values

Here I use OFFSET together with COUNTIF and OFFSET again, to get a drop-down list that is restricted to the non-empty cells. I will be making a separate video on this in the coming months to explain the technique used in more detail.

AND finally in the end we get to our dependent drop down list :)

Watch the steps in this video:

Feel free to Download the Workbook HERE.

Free Excel Download

Excel Dashboards that Inform & Impress

Use these techniques in your own reports

Unbeatable value!

LEARN MORE

19 Comments

  1. Ismail Ismaili March 16, 2018 at 1:47 pm - Reply

    thank you so much Ms. Leila you are so good teacher.

    • Leila Gharani March 20, 2018 at 8:08 am - Reply

      Thank you very much Ismail.

  2. Patrick O'Donnell April 24, 2018 at 8:53 pm - Reply

    These tutorials are amazing and have helped me a great deal in understanding Excel more. I have a quick question regarding this tutorial. If I wanted to include a third column of data, and exclude duplicates from the first and second columns, is there an easy way to address that? For instance if you had duplicates in your app column, and added a third column that does not have duplicates. For instance if you had a first column listed as “Division”, second as “App” which has duplicate values, and third as name assigned where no duplicates take place. The end goal is to have two dependent drop downs after the first drop down is established with the second drop down excluding duplicates and the third showing all as there is no duplicate. Any help would be greatly appreciated, or possibly if you’ve already addressed such an instance in another tutorial. Thank you so much for the tutorials you’ve put up!

    • Leila Gharani April 26, 2018 at 8:24 pm - Reply

      Hi Patrick – I think this video might have the answer: https://youtu.be/gu4xJWAIal8

      • Amanda May 30, 2018 at 7:21 pm - Reply

        This still only shows two columns of Data. I am with Patrick and need three columns where the second also has multiple instances.

    • Deepthi May 8, 2018 at 10:11 am - Reply

      Even i to want same requirement, if you got it just reply back

  3. hany May 8, 2018 at 7:45 pm - Reply

    your excel window open the nice vision to me

  4. Osama M May 18, 2018 at 9:46 pm - Reply

    Hi Leila, thank you very much. Your tutorial and files are extremely helpful, it’s people like you who make a good change in this world believe it or not.
    Thank you, love and kisses

    • Leila Gharani May 23, 2018 at 5:40 pm - Reply

      Thank you very much Osama for the kind words. I’m really happy to hear you find the tutorials and Workbooks useful. Wish you all the best, Leila

  5. Ahmad Assalem August 23, 2018 at 12:24 pm - Reply

    Ms. Leila. thank you. very big thank you for those tutorials. you helped me to find solutions in EXCEL. Those tutorials are extremely helpful. Although I’m not a native English speaker. your simple language and the quietly way you are delivering ideas. All helped me to understand easily. I want to share how your lessons improved my knowledge & found solution for my projects. So, I made this file. which contains same idea in this tutorial . with extra column. Thank you

    https://www.dropbox.com/s/47bctlhvyfbpvxc/Excel_Unique_Values_for_Dropdown_XelPlus_forApp.xlsx?dl=0

    • Ahmad Assalem August 23, 2018 at 12:29 pm - Reply

      I used CSE functions. I think you can help to improve them and use non CSE formulas.

    • Leila Gharani August 23, 2018 at 7:01 pm - Reply

      Thank you for sharing your file. It’s great! I’m glad you find the tutorials useful.

    • Lynda January 31, 2019 at 8:40 pm - Reply

      I downloaded your file… its exactly what I am look for, but don’t understand it, i am making a file with multiple drop down lists that rely on each other, (average fuel milage on cars, depending on year, model, cylinder, etc) can I reach you for help on this?

      • Leila Gharani February 9, 2019 at 6:04 pm - Reply

        Hi Lynda – check out this post from Debra for multiple drop-down lists.

  6. Aloysius September 19, 2018 at 3:05 pm - Reply

    Hi Liela,

    The tutorial are amazing. What about if we want 3 drop-down all depending on each other.

  7. Rebecca Narcis May 8, 2019 at 5:53 am - Reply

    Hi Liela,

    This tutorial is what I am looking for but my version does not have aggregate function. How do I work around it?

    Thank you so much!

    • Leila Gharani May 16, 2019 at 4:31 am - Reply

      You can use the large function – you just need to use control + shift + enter after you type in the function.

  8. Praneeth June 11, 2019 at 11:37 am - Reply

    Hi Liela,

    Your tutorials are impressive.
    I am using excel 2007 and AGGREGATE function not available. any suggestions.

    PS

    • Leila Gharani July 4, 2019 at 6:51 am - Reply

      I use aggregate to avoid CSE (control shift enter) – but you can use offset with helper cells (I’ll make a video on this one soon) or you can use the large function with CSE….

Leave A Comment

Share This