How to Extract Data Based on a Drop Down List Selection in Excel

To get a better visualization and comparison, we may need to extract some specific data based on certain criteria. In this article, we will show you how to extract data based on a drop-down list selection in Excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


6 Steps to Extract Data Based on a Drop Down List Selection in Excel

In the image below, a sample data set is provided to accomplish the tutorial to show how to extract data from the drop-down list. We will use Data Validation to make a drop-down list. Later on, we will use the FILTER function, to filter the extracted data.

Extract Data Based on a Drop Down List Selection in Excel

Step 1: Create a Table to Extract Data Based on a Drop Down List Selection in Excel

  • Select the Table.

Extract Data Based on a Drop Down List Selection in Excel

  • Click on the Insert.

Extract Data Based on a Drop Down List Selection in Excel

  • Click on the Table Design, and give a name (Sales) to it.

Extract Data Based on a Drop Down List Selection in Excel


Step 2: Extract the Unique Data Based on a Drop Down List Selection in Excel

  • To make a list with the unique Values in the Branch column, apply the formula of the UNIQUE.
=UNIQUE(Sales[Branch])

Extract Data Based on a Drop Down List Selection in Excel

  • Therefore, you will get the unique values for the Branch.

Extract Data Based on a Drop Down List Selection in Excel


Step 3: Insert a Data Validation List to Find Data Based on a Drop Down List Selection in Excel

  • To create a Data Validation list, click on Data.
  • Then, click on the Data Validation.

Extract Data Based on a Drop Down List Selection in Excel

  • Select the List from the Allow.
  • Press Enter.

Extract Data Based on a Drop Down List Selection in Excel

  • In the source box, select the List.
  • Finally, press Enter.

Extract Data Based on a Drop Down List Selection in Excel

  • As a result, you will see that, Data Validation drop down list is created.

Extract Data Based on a Drop Down List Selection in Excel


Step 4: Apply the FILTER Function to Extract Data Based on a Drop Down List Selection in Excel

  • In the FILTER Function, add the Table ‘Sales’ as the array element by using the formula.
=FILTER(Sales

Extract Data Based on a Drop Down List Selection in Excel

  • In the Include argument, add the Branch Use the following formula.
=FILTER(Sales,Sales[Branch] = H4
  • H4 is the cell of the drop-down selection box.

Extract Data Based on a Drop Down List Selection in Excel

  • In the ‘if empty’ argument, type “Nothing Found”.
=FILTER(Sales,Sales[Branch] = H4,"Nothing Found")

Extract Data Based on a Drop Down List Selection in Excel

  • Now, select any option (Texas), to extract all the related value.

Extract Data Based on a Drop Down List Selection in Excel

  • Therefore, you will find the all the values regarding ‘Texas’.

Sample Data

Notes. The FILTER function is only available in Microsoft 365.


Step 5: Insert Another Criterion to Extract Data Based on a Drop Down List Selection

  • To insert another criterion, make a unique list with another column (Products). Type the formula in a cell.
=UNIQUE(Sales[Products])

Sample Data

  • Therefore, another unique list will be created for the ‘Products‘ column.

Sample Data

  • Make another Data Validation drop down list with selecting the cell values.
  • Then, press Enter.

Sample Data


Step 6: For Multiple Criteria Extract Data Based on a Drop Down Selection List in Excel

  • After creating another drop down list, it will show like the image below.

Sample Data

  • Type the following formula to apply both the criteria.
=FILTER(Sales,(Sales[Branch] = H4)*(Sales[Products]=H6),"Nothing Found")

Sample Data

  • Select any two options from the two drop down list.

Sample Data

  • As a result, you will get the value of the rows, satisfied both the criteria.

Sample Data


Conclusion

Finally, I hope you now know how to convert text to date using Excel VBA. All of these tactics should be performed while your data is being educated and practiced. Look over the practice book and put what you’ve learned to use. Because of your generous support, we are motivated to continue delivering initiatives like these.

Please do not hesitate to contact us if you have any questions. Please let us know what you think in the comments area below.

The Exceldemy team will respond as quickly as possible.

Stay with us and keep learning.

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo