Contents
Classic dropdown list in an Excel sheet cell made via Data – Validation (Data — Validation) – 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 the list contains only a couple of dozen positions, 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 ultimate goal is to create a drop-down list (cell G3) where you can quickly find the movies you need by entering just the genre, year, or part of the title, such as “gump”.
Step 1. Determine who we need
First we need to understand which of the source cells should be shown 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 function SEARCH (SEARCH), 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:
Now let’s wrap our formula in a validation function ISNUMBER (ISNUMBER), which will turn numbers into logical TRUE (TRUE), and errors to FALSE (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 function IF (IF) checks if 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 a banal function VPR (VLOOKUP) just display all found names (I added a column with ordinal numbers for convenience):
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 on the tab formula teams Name Manager – Create (Formulas — Name Manager — Create):
The range name can be anything (for example, Films), and the most important is the function DISPOSAL (OFFSET)which does all the work. Let me remind you of its syntax, if you forgot:
=DISPLACEMENT(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 value of the index 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 on the tab Data Command Data validation (Data — Validation). In the window that opens, select List (List) in the field Data type (Allow), and as the source, enter the name of our created range with an equal sign in front of it:
So that Excel does not swear when typing for an inexact match of our phrases with the original list, on the tab Error message (Error Alert) in this window, you need to uncheck the box Display an error message (Show error alert):
That’s all. You can press on OK and enjoy the result:
For greater convenience when entering from the keyboard, you can use Ctrl+Enter instead Enter after entering the text (so the active cell does not go down) and the keyboard shortcut Alt+arrow to downto expand the dropdown list without a mouse.
PS
In principle, one could not continue, but recently Microsoft rolled out an update to the Excel calculation engine, which now supports dynamic arrays and has special functions for working with them. They will become available to most users in the coming months, but even if these features are not yet available in your Excel, it’s a sin not to show how elementary our task is solved with their help.
Everything that we did in Steps 1-3 is replaced by one (!) formula, where the new function FILTER (FILTER) selects from the source 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:
And that’s it. No named ranges, no slow OFFSETs, no dancing with extra columns and formulas. Song!
- What are dynamic arrays in Excel
- Analysis of the three main functions of dynamic arrays: SORT, FILTER and UNIC
- 4 Ways to Create a Dropdown List in an Excel Sheet