0

Searchable Drop-Down List In Excel

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.

1. Data Validation List Introduction – http://blog.thejaytray.com/data-validation-list-excel-introduction/
2. List Based On A Range Of Cells – http://blog.thejaytray.com/data-validation-list-based-range-cells/
3. Automatically Updated Source – http://blog.thejaytray.com/data-validation-list-automatically-update-source-range/
4. 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:

1. Check each item in the original list against the search field
2. Assign a sequential number to each relevant item found in the original list
3. Create a smaller list of items based on the search field
4. Create a Named Range for my drop-down list
5. 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