Microsoft Excel searchable drop down list without VBA

https://www.youtube.com/watch?v=INFoqDqkhLk

The classic drop-down list in an Excel sheet cell, made through Data - Validation, is a simple and convenient thing that many users use every day. However, this list has one very serious drawback - it does not have a quick search by the first characters, i.e. filtering (selecting) only those values ​​where the input fragment is included as a substring. This seriously impairs usability even if there are only a couple of dozen positions in the list, and with several hundred it kills usability completely.

Let's look at how to implement such a trick. As a guinea pig, let's take the list of the 250 best films according to IMDb.

The end goal is to create a drop-down list (cell G3) where you can quickly find the movies you want by entering just the genre, year, or part of the title, such as "gump".

Determine who we need First we need to understand which of the source cells to show in the list, i.e. determine whether the text entered in the drop-down list (for example, the genre "detective") is contained in the title of the movie.

To do this, add another column to the left of the original data with the SEARCH function, which searches for a given substring in the text and returns either the ordinal number of the character where it was found, or an error if it is not there.

Then wrap our formula in an ISNUMBER test function that will turn numbers to boolean TRUE and errors to FALSE

Now let's make it so that FALSE turns into 0, and instead of TRUE, successively increasing indices-numbers 1,2,3 ... etc. appear in the column.This can be done by adding a couple more functions to our formula.

Here the IF function checks what we have (TRUE or FALSE), and if it was TRUE, then displays the maximum value of all higher numbers + 1 if it was FALSE, then outputs 0

Step 2. We select in a separate list Further - easier. Now, with the banal VLOOKUP function, we simply display all the found names.

After that, you can play around by entering different words and phrases into the yellow cell G2 and watch how our formulas select only suitable films.

Step 3: Create a Named Range

Now let's create a named range that will refer to the selected movies. To do this, select the Name Manager - Create command on the Formulas tab. Create a named range. The name of the range can be anything (for example, Movies), and the most important thing is the OFFSET function, which does all the work.

=OFFSET(start_cell; shift_down; shift_right; height; width)

We have: As the starting cell, we set the first cell of the list of selected elements (E2). We have no shifts down and to the right, i.e. are equal to zero. The height of the range we have corresponds to the maximum index value from column A. The range width is 1 column. It remains to make a drop-down list.

Step 4. Create a dropdown list Select the yellow cell (G2) and select the Validation command on the Data tab.In the window that opens, select List (List) in the Data Type (Allow) field, and as a source, enter the name of our created range with an equal sign in front of it. So that Excel does not swear at the inexact match of our phrases with the original list, on the Error Alert tab in this window, you need to turn off the Show error alert checkbox. Turning off the error message For more convenience when typing, you can use Ctrl+Enter instead of Enter after entering text (so the active cell does not go down) and Alt+Down Arrow to expand the dropdown list without a mouse.

Everything that we did in Steps 1-3 is replaced by one formula, where the new FILTER function selects from the original range A2:A251 only those movies that contain the given substring. And then, when creating a drop-down list, specify the first cell of the range of selected films (C2) as the source and add the # sign to it to get a link to the entire dynamic array

Finally we get SEARCHABLE drop down lists in Microsoft Excel WITHOUT writing complex formulas. There's no VBA, no filter function. The autocomplete functionality is there by default. The ability to search for values in data validation was released on the web (online) version of Excel last year but now it's available in the Excel desktop version as well. To search the drop-down list you just have to start typing in the cell. A list of search results will appear and filtered down as you type. It also automatically suppresses empty cells.

You can use this to create multiple searchable drop-down lists or data validations in a single sheet.

This update is currently available on the Beta Channel for Office 365 Insiders for Windows and Mac.