Searchable drop down list in Excel 2010

Hi All,

Table of Contents Show

Before You Begin…
How To Create A Searchable Drop-Down List In Excel
Check each item in the original list against the search field
Assign a sequential number to each relevant item found in the original list
Create a smaller list of items based on the search field
Create a Named Range for my drop-down list
Create and edit the Data Validation options to finalise the file
How can I create a drop down list in Excel 2010?
What version of Excel has searchable drop down list?
How do I add a search option to a drop down list?

I'm trying to set up a searchable dropdown list. It'll effectively be for searching for clients by name.

I am trying to avoid using the filter function as not everyone will access the files in 365/web version.

https://www.bluepecantraining.com/create-a-searchable-drop-down-in-excel/

I've used the formulas from the video/workbook linked above.

It's almost working. As best I can tell, the issue lies in the "Workings" column.

Here's the formula from his sheet.

=IF(ISNUMBER(SEARCH(‘Drop Down List’!$A$2,[@Product])),MAX($B$1:B1)+1,0)

This spits out the occurrences of the letters typed in his form (so If I type d, it returns 1 for the first d, 2 for the second, 3 for the third, etc)

When I set up the same formula in my spreadsheet, it is only returning 1s. So it just counts 1 everytime the letter appears, not 1,2,3 etc.

That seems to break the rest of the formulas.

Any help greatly appreciated.

Using data validation you can create useful drop-down lists in Excel. If your list contains lots of values, it might be worth making it searchable. The video below explains how this can be done – no VBA required! Download the featured file here.

Formulas used in the video

Workings column
=IF(ISNUMBER(SEARCH(‘Drop Down List’!$A$2,[@Product])),MAX($B$1:B1)+1,0)

Drop down list values
=IFNA(INDEX(ProductList[Product],MATCH(ROW(D1),ProductList[Workings],0)),””)

Named Reference Formula
=OFFSET(‘Product List’!$D$2,,,MAX(ProductList[Workings]))

This post will tell you how to create a searchable drop-down list in Excel.

After my earlier series of posts on Data Validation and drop-down lists, I received requests for further help and how to create a searchable drop-down list in Excel.

The image above shows a searchable drop-down list. The letters PP are entered in the cell and the drop-down list shows only the items containing PP.

A normal drop-down list would not allow you to do this. The list would contain all items (see the next image) and if you tried to search PP you would get an error message (see the second image below).

For this post, I have my original list, my helper columns and my search field all on the same sheet. However, in reality I would have the list and helper columns on one sheet, usually hidden from the end user and they would be using the searchable drop-down list on another sheet.

Before You Begin…

I would advise you to read the earlier posts on Data Validation and drop-down lists, it is not necessary to do this, but they can give you an introduction to this topic.

Data Validation List Introduction – http://blog.thejaytray.com/data-validation-list-excel-introduction/
List Based On A Range Of Cells – http://blog.thejaytray.com/data-validation-list-based-range-cells/
Automatically Updated Source – http://blog.thejaytray.com/data-validation-list-automatically-update-source-range/
List Based On Criteria – http://blog.thejaytray.com/data-validation-list-based-criteria/

How To Create A Searchable Drop-Down List In Excel

In my example I start with a list of 88 fruits and vegetables. I will then use some “helper” columns to create a smaller list of fruits and vegetables based on my search. The steps involved are to:

Check each item in the original list against the search field
Assign a sequential number to each relevant item found in the original list
Create a smaller list of items based on the search field
Create a Named Range for my drop-down list
Create and edit the Data Validation options to finalise the file

The items below will make up my list. They will be listed down in Column A of my file.

While building my file, I will start with something like this

Column-A is my full list
Column-B will be used to check each item in column A against the search field in cell H2
Column-C will assign a sequential number to each item in column A that meets our search field
Column-D will be the shortened list, based on our search field, and our Data Validation will point to this list when completed.
Cell H2 is our search field. Initially this will be a blank field, at this point we are not going to apply Data Validation to it.

Some of the formula we are about to use can look confusing if you’re not used to it, so I will start each one with a base formula and then add to it so we end up with the formula we need.

If you’re ready, let’s begin.

Check each item in the original list against the search field

To get us started, let’s enter an L in our search field (cell H2)
In cell B2 we will enter a formula that will search for the value in H2 within the text in cell A2 =SEARCH($H$2,A2).
When we copy down the formula, we want all rows to be referencing cell H2, so make sure H2 is an absolute reference, with the $ sign before both the H and 2.

In B2 you can see the result of the formula is 4, the L is the 4th character in APPLE which is in cell A2
In B3, you get the #VALUE! error, this is because the L can’t be found in APRICOT which is in cell A3
We are going to add to the formula now in cell B2. AFTER THE = SIGN, ADD “IF(ISERROR(” and then at the end of our formula add”,0,1)”. Our full formula is =IF(ISERROR(SEARCH($H$2,A2)),0,1)
What is this doing? It is telling Excel, that if our SEARCH formula returns an error, then we want our formula result to be 0, or else we want our formula result to be 1.
Copy this formula down column B, and let’s change our search field (H2) to search for the letter E

You can see above that any item in column A that contains and E has a 1 beside it in column B, it doesn’t matter if the E is the 1st, 2nd, 3rd or 10th character, it will return a 1.

This formula is then used in our next step.

Assign a sequential number to each relevant item found in the original list

In Column C we will add a COUNTIF function to count how many 1’s we have in Column B up to a point.

In cell C2 enter the function =COUNTIF($B$2:B2,1)
Copy this formula down to the bottom of the list
My search field in H2 is looking for the letter E

What is our formula result telling us? We made our first B2 reference in our formula an absolute reference $B$2. So, when we copy this formula down the first reference will always be B2 and the second cell reference in our function will change to B3, B4, B5 and so on.

Between B2 and B2, the number 1 appears once, so our formula result is 1. 1 doesn’t appear in B3, our formula is counting the 1’s between B2 and B3 now, there is still only 1.

When we get to B4, we have a second 1 between B2 and B4, so our formula result is 2 now, and so on.

Next we will add to our existing formula. We only want a number beside a fruit/veg that is relevant to our search. So, I want to get rid of the second 1, the second 3, the second, third and fourth 5, etc.

To do this we are going to amend our formula and say that if the value in column B is 0 (that is, the fruit/veg for that row is not relevant to our search field) then our formula in column C should return blank, otherwise then use the COUNTIF function we have already.

In cell C2, after the = sign enter IF(B2=0,””, and add a closing bracket to the end )
The full function in C2 should be =IF(B2=0,””,COUNTIF($B$2:B2,1))

How are we going to use this formula?

In column D in the next steps we will be creating the shortened list that will appear in our drop-down. The fruit/veg with 1 in column C will be the first result on our shortened list, the fruit/veg with a 2 in column C will be the second item on our shortened list.

Create a smaller list of items based on the search field

In Column D, we will use INDEX-MATCH to create our shortened list that will appear in our searchable drop-down list.

In D2 we will enter our first formula, we want to look up the number 1 in column C and return the name of the fruit/veg from column A.

Then in our next formula we want to look up number 2 in column C.

A straight INDEX-MATCH to find the number 1 in column C and return the value from Column A would be =INDEX(A:A,MATCH(1,C:C,0)). If you enter this in D2 and copy it down for all rows you end up with this…

All our INDEX-MATCH functions are looking up 1 in column C. Without having to amend each formula, we want our second INDEX-MATCH to look up 2 in Column C, then 3, then 4, then 5, etc.

To do this, we will replace our 1 from the first function with ROW(A1) and copy this down.

The Row number of cell A1 is 1, so this function would return the result 1. Note the remaining functions after you have copied them down, A1 changes to A2, then A3, then A4 and so on. The results of these functions will be 2, 3, 4, etc. We don’t have to manually adjust each formula now to look up the next sequential number.

Try changing your search field and watch the results.

So far, for simplicity i have used just one letter in my search field, if I change this to PP I get the result…

Note, when it runs out of results, all my remaining formula in column D result in #N/A.

To fix this, we will add an IFERROR for our function to return blanks if the INDEX-MATCH returns an error values.
In cell D2, after the = sign, enter IFERROR(
At the end of the formula enter ,””)
The full function should be =IFERROR(INDEX(A:A,MATCH(ROW(A1),C:C,0)),””)

There is our shortened drop-down list that we want the user to see.

Create a Named Range for my drop-down list

Because our list in Column D can change size, and we don’t want the end user to see a long scroll list with just a small number of values, we will create a named range that will change in size to match our list in column D.

Our Named Range will start from cell D2 and will be X rows long. The X depends on how many items are in our shortened list, to get this number we will use another COUNTIF function.

This is just for demonstration, it will not be part of our end file, but in cell E3 I have entered the function =COUNTIF(D:D,”?*”)-1.

This will count how many cells in column D contain any character in any position (?*), and then deduct 1 because we don’t want to include the header “My_List” in the result.

This idea will be used to calculate the height of our named range.

Go to Formulas in your menu/ribbon and select Name Manager
Select New

Name it “My_List”
Delete what is in the Refers to field and we will enter an OFFSET function
Type =OFFSET(
Select the first cell of your shortened list (D2 in my case)
Enter 3 commas ,,,
Enter COUNTIF(
Highlight the column of your shortened list (F:F in my case)
Enter ,”?*”)-1)
The name of the sheet I’m working on is Stage7, so my full OFFSET function is =OFFSET(Stage7!$D$2,,,COUNTIF(Stage7!$D:$D,”?*”)-1)
Close the Name Manager

Create and edit the Data Validation options to finalise the file

Now, let’s use this Named Range as our Data Validation List.

Click on your search field (H2 in my case here)
Go to the Data menu and select Data Validation
Under “Allow” select List
For Source enter =My_List, or whatever your named range is

If you clicked OK (and you can if you wanted to test this) and entered PP in your search field and clicked the drop-down button you would get an error message.

We need to turn off the Error Alert in the Data Validation set-up.

Go to the Error Alert tab and untick the “Show error alert after invalid data is entered” box

Now click OK and try out your searchable drop-down list.

Enter some search text in your field and then click the drop-down arrow, you should only see results that contain your search criteria.

Next Steps

To give a simple demonstration of how I use this i the real world (I don’t want the end user seeing helper columns that might confuse them, or tempting them to amend/sort any helper functions) I would add a searchable drop-down list to a sheet that the user will be working on.

I’ve added a sheet to my file called “Search”
Cell A1 has the text “My favourite fruit/veg is”
Cell B2 will be my search field

I need to make a change to my Search function in my helper column
Instead of searching for the text in H2, I want it to search for the text in B2 of my Search sheet, so I update this formula accordingly

Now I can hide all sheets not needed, and the file is ready for the end user to work on

How can I create a drop down list in Excel 2010?

Create a drop-down list.

In a new worksheet, type the entries you want to appear in your drop-down list. ... .

Select the cell in the worksheet where you want the drop-down list..

Go to the Data tab on the Ribbon, then Data Validation . ... .

On the Settings tab, in the Allow box, click List ..

What version of Excel has searchable drop down list?

Excel..

Microsoft 365 and Office..

How do I add a search option to a drop down list?

Use any element to open the dropdown menu, e.g. a <button>, <a> or <p> element. Use a container element (like <div>) to create the dropdown menu and add the dropdown links inside it. Wrap a <div> element around the button and the <div> to position the dropdown menu correctly with CSS.