Excel auto find drop down menu

Create a Named range

To manage the named ranges, you can go in Formulas > Name Manager. You can also use defined range by their name in Excel formulas.

1. Static

To create a named range in excel, you can select a column of data then right click then select define name the name will be the name of the range and how it will be referred to. The refers to is the range itself and is auto populated with the range of selected cells when clicking on define name

2. Dynamic

To get a dynamic named range you will need to replace the refers to of the named range by this kind of formula (for example if values are in the A column):

=$A$2:INDEX($A$2:$A$100;COUNTIF($A$2:$A$100;"<>"&""))

This formula will start looking at value from A2 to the index (the coordinates) of the last non-empty cell (up to 100 in here). It will only refers to the populated cells in the dynamic named range.

Get other information from the entered item

1. Example:
  • You have a range of value with a defined name: list with all the values to find.
  • You have the case where the search value is entered in C4.

Then you can add this formula in the cells next to C4 to map the cell using what has been entered in C4.

=INDIRECT("tab_name!"&ADDRESS(MATCH(C4;List;0)+1;COLUMN(List)-1))
  • The MATCH function will match the ROW of the entered value (here C4) and the List value to get the right one.
  • the ADDRESS function will map the found value and its relative position. (Used with +1 or -1 in the ROW, COLUMN you can modify the address you get).
  • the INDIRECT function print the value of the input coordinates (the "tab_name!" where the value is, and the address of the found value).
2. Another Example

Or you can use this formula which will look in List if it finds the value in C4:

=VLOOKUP(C4;List;2;FALSE)

Here is a sweet example that requires 1 column with the values and 3 helping columns, and a cell that will be used to do the Google-like search:

E. Available values F. criteria matching G. Occurrence count H. Found values
value_one 1 1 value_one
value_two 0   value_three
value_three 1 2  
  • Column #1 : Available Values you add the values that will be looked at
  • Column #2 : Criteria matching you add this formula:
=ISNUMBER(IFERROR(SEARCH($B$3,E3,1);""))

This formula returns 1 if part of what is in cell E3 in the Available values column is also in cell B3, the search cell.

  • Column #3 : Occurrence count you add this formula:
=IF(F3=1;COUNTIF($F$3:F3,1);"")

This formula starting at F3, with F3 the criteria matching look if the criteria matching is 1 and count how many there was since first cell ($F$3).

  • Column #4 : Found Values stack all the criteria matching values with this formula:
=IFERROR(INDEX($E$3:$E$22,MATCH(ROWS($G$3:G3),$G$3:$G$22,0)),"")

With G3 in the Occurrence count column. It works with MATCH and INDEX looking for occurrence. The IFERROR will show the corresponding value indexed, or nothing.

You can use this formula to create the dynamic range from the found values in H3:

=$H$3:INDEX($H$3:$H$22;MAX($G$3:$G$22);1)

The name will be used for the combobox (dropdown in developer > insert > activeX). Here are the properties to look for:

  • AutoWordSelect: False
  • LinkedCell: B3
  • ListFillRange: name of the created named range
  • MatchEntry: 2 – fmMatchEntryNone

The LinkedCell B3 is the searching cell, it will print the result of the search. If you haven’t change the name of the combobox, the default one should be ComboBox1 and you can copy and paste that into the VBA part of your sheet:

Private Sub ComboBox1_Change()
'DropDownList is the name of the created Named range
ComboBox1.ListFillRange = "DropDownList"
Me.ComboBox1.DropDown
End Sub

This sub ComboBox1_change() overwrites the default attitude of the ComboBox object when changed.

You also need some lists for the comboBox to update automatically, one for the dropdown that will be the results of the matched value of the occurrence count.