In this tutorial, I will show you how to create a drop-down filter in Excel so that you can extract data based on the selection from the drop-down. As shown in the pic below, I have a created a drop-down list with country names. As soon as I select any country from the drop-down, the data for that country gets extracted to the right.

Note that as soon as I select India from the drop-down filter, all the records for India are extracted. Download the Example File

Extract Data from Drop Down List Selection in Excel

Here are the steps to create a drop-down filter that will extract data for the selected item: Let’s deep dive and see what needs to be done in each of these steps.

Create a Unique List of Items

While there could be repetitions of an item in your dataset, we need unique item names so that we can create a drop down filter using it. In the above example, the first step is to get the unique list of all the countries. Here are the steps to get a unique list:

Now we will use this unique list to create the drop-down list. See Also: The Ultimate Guide to Find and Remove Duplicates in Excel.

Creating the Drop Down Filter

Here are the steps to create a drop down list in a cell:

The goal now is to select any country from the drop-down list, and that should give us the list of records for the country. To do this, we would need to use helper columns and formulas.

Create Helper Columns to Extract the Records for the Selected Item

As soon as you make the selection from the drop down, you need Excel to automatically identify the records that belong to that selected item. This can be done using three helper columns. Here are the steps to create helper columns:

Helper Column #1 – Enter the serial number for all the records (20 in this case, you can use ROWS() function to do this). Helper Column #2 – Use this simple IF Function function: =IF(D4=$H$2,E4,””) This formula checks whether the country in the first row matches the one in the drop down menu. So if I select India, It checks whether the first row has India as the country or not. If it’s True, it returns that row number, else it returns blank (“”). Now when we select any country, only those row numbers are displayed (in the second helper column) which has the selected country in it. (For example, if India is selected, then it will look like the pic below).

Now we need to extract the data for these rows only, which displays the number (as it is the row that contains that country). However, we want those records without the blanks one after the other. This can be done using a third helper column

Third Helper Column – Use the following combination of IFERROR and SMALL functions: =IFERROR(SMALL($F$4:$F$23,E4),””)

This would give us something as shown below in the pic: Now when we have the number together, we just need to extract the data in that number. This can be done easily using the INDEX function (use this formula in the cells where you need the result extracted): =IFERROR(INDEX($B$4:$D$23,$G4,COLUMNS($J$3:J3)),””) This formula has 2 parts: INDEX – This extracts the data based on the row number IFERROR – This function returns blank when there is no data Here is a snapshot of what you finally get:

You can now hide the original data if you want. Also, you can have the original data and extracted data in two different worksheets as well. Go ahead. use this technique, and impress your boss and colleagues (a little show-off is never a bad thing). Download the Example File Did you like the tutorial? Let me know your thoughts in the comments section. You May Also Find the Following Tutorials Useful:

Dynamic Excel Filter – Extract Data as you type. Dynamic Search in Excel Using Conditional Formatting. Create Dynamic Drop Down with Search Suggestions. How to Extract a Substring in Excel Using Formulas. How to Filter Cells with Bold Font Formatting in Excel.

Is there a away to show you the sheet and help me to have more than one drop down list in the formula of Helper Thank you 2. please help. I can send you a file of where I’m currently at, please let me know where to send it. regards Steve I need a favor of yours. I have just implemented the same into Google spreadsheet and it’s creating an issue. I and created the same in Excel and it’s working fine. In google sheet, the logic =IFERROR(INDEX(Data!$A$4:$C$52,Data!F4,1),””) is not working especially when there is no reference instead of printing blank it’s breaking. Please let me know if you have any solution here. Thanks in advance. I’m now wondering if there is a way to link 2 or more drop down lists for one data table to dynamically update based on options selected within multiple down lists. I’m guessing there must be a way to amend the following formula “=INDEX(‘Table1′!$F$7:$L$5654,’Table1’!$N7,COLUMNS($G$8:G8))” to expand on the dropdown lists used to update the data tables. Are you able to advise how I should go about achieving this or point me in the right direction of where I can find tutorials around this please? Any help / advise would be greatly appreciated. Many thanks, Mike. =IFERROR(SMALL($Q$2:$Q$1048,ROWS($Q$2:Q2)),””) =IFERROR(SMALL($Q$2:$Q$1048,ROWS($Q$2:Q2)),””) I just have one more question, what if i want to add one more column after sales rep column, what is the formula for that? My requirement is… when i select a value on column A, then column B should list only the values related to Column A ColumnA ColumnB 123 1 123 2 234 1 234 2 345 1 456 1 567 1 678 1 Expected Result ColumnA (drop down) ColumnB (drop down) 123 1 2 My requirement is… when i select a value on column A, then column B should list only the values related to Column A ColumnA ColumnB 123 1 123 2 234 1 234 2 345 1 456 1 567 1 678 1 Expected Result ColumnA (drop down) ColumnB (drop down) 123 1 2 Thank you Request you to please share same process in VBA code. Email.id : sachin.nikam@hungama.com Thank you so much for your explanation, it is great! I am using a file which doesn’t bring country list; however, brings some information other spreadsheet. Anyway it is not working, the “helper 3” brings the information, but doesn’t show up on “Product name or Sales Rep” and I do not know what I made wrong. Can you please help me? I really got stuck on these files, 2 weeks already 🙁 Thank you, Cris I am having a problem with cross referencing the data. For example I want to see all the people from a certain district and then filter the results by how many male/female in that district. When I try this it doesn’t work, I believe it has something to do with the ‘helper’ columns. Do you know how to make this work? I’m trying to make a excel sheet with product information witch can sort out and display products witch match certain criteria. Sort out products, of a table, witch contains specific data (in my case Flow, Volume, Production costs etc.). My goal is to have a worksheet with my company’s old work (I work with water cleaning systems) and with this worksheet sort out all the water cleaning systems witch match my search, and display those in some way. And then automaticly calculate a price based on those. I want to able to have multiple drop downs to make my search narrower. I have tried slicers but i can’t get it to work and display multiple matches. Maybe it’s easier with drop down lists? IF B2 = MTH (X) B5 =IFERROR(INDEX(‘DUES MTH (X)’!$E$4:’DUES MTH (x)’!$AI$68,’DUES MTH (x)’!$C4, COLUMNS($B$5)),””) =IFERROR(INDEX(‘DUES MTH 1′!$E$4:’DUES MTH 1′!$AI$68,’DUES MTH 1’!$C4, COLUMNS($B$5)),””) I’m looking for help, I’m a complete newbie at excel so struggling to create something similar to this but its much more basic. i need 1 list (data validation) which i worked out how to do, and i need it to extract information from 1 row. there are no duplicates, no multiple entries. just a simple drop down list that brings up a few columns of data in a row. example: NAME l PHONE l ID Number l steven l 07827288292 l 4332 l so i would click a name and it would return his personal data, i have about 60 names i need to do this with. i really need your help with this. thank you Steven. Hope this helps! I can see how you did it now. Thank you. In Helper 3: =IFERROR(SMALL($F$4:$F$103,E4),””) Formula to extract data (in J4 which can be copied/dragged to all other cells): =IFERROR(INDEX($B$4:$D$103,$G4,COLUMNS($J$3:J3)),””) Love this model and want to build something that may be able to handle up to 76 columns of criteria!! E.g. if I choose (e.g. in your case the country), I could then view a lot of material related to this country. Also would it even be possible to put the countries at the top and the profiling criteria down the column? Thanks so much, To have country at top and profiling criteria at the bottom, you can use a dependent drop down list – http://trumpexcel.com/2013/07/creating-a-dependent-validation-drop-down-list/ Hope this helps!! Also, since you have a lot of data, I recommend use helper column approach instead of formula (as shown in Formula Hack #17). My conundrum is how to base the population based on a drop down box selection of list titles.

  1. I select governance from a drop down list of (e.g Governance, Finance, HR etc. ) 2. I can transpose the named ranges which will be titled Governance, Finance, HR etc.) I can type in the name of the list, e.g. Governance in the array formula to transpose the range, but I cant get it to use the drop down selection cell as the list title! Do you think you could help? But its not very elegant to say the least ! If you have a better way do please let me know!! My formula works beautifully when I select a function with 7 range criteria, but when I select a function with only 3 or 4 the array formula brings back more information than I need and is not bringing back a null or false value for the other 3 or 4 cells I shouldnt have range criteria for 🙁 I’m nearly there but not quite! Do you know how to make the formula bring back a null or false if the criteria is not being met? Now when you select ‘All Countries’ from the drop down, all the countries will be displayed