how to make a drop down list in excel with multiple columns

MATCH function will look-up for”0” in the output of COUNTIF and returns the row number which has the next unique brand name.

COUNTIF function would return the number of occurrences of the in the sample data range.

Write the below formula in the “A2” cell of “Temp” sheet and remember to press Ctrl + Shift + Enter! Drag the formula till “A11” cell of “Temp” sheet. The below formula can be used to get the unique values from column A of ‘Sample Data’. Another option to get the unique list would be to use array formulas. But using that is a manual task and must be repeated, when new data would be added. One option that is often used is ‘Remove duplicates’ feature from ‘Data’ tab of Excel’s menu ribbon. There are many ways to make a list unique. We need to eliminate them before we can show it in our first drop-down. In Column A of ‘Sample Data’, you can see there are repeated values. To create these drop-down lists, I will be using two sheets: “ Sample Data” & “ Temp”.Ĭreating a list with unique values for first drop-down Using the above data, we want to create dependent drop-downs like shown below. In the formula bar, you can see any array formula wrapped inside curly braces Array formulas must be entered in a cell by pressing Ctrl + Shift + Enter. Likewise, an array formula can return one or more items as result.

An array is a collection of one or more items. What I mean by multiple dependent drop-downs is: When selecting an item from the first drop-down, the following drop-downs would show only relevant items based on your selection.Ĭreating dependent drop-down lists in excel could be easy if done using VBA, but it becomes trickier to do it with excel formulas alone. In this article, we’ll learn how to create multiple dependent drop-downs using array formulas. Drop-down lists are a very important part of any dashboard or data entry form.

How to make a drop down list in excel with multiple columns