Welcome to Excel Jet Consult blog post. In this blog post, we will learn how to create dynamic searchable dropdown in Excel for the Web. Let’s get started
Video
Sample Dataset
In the screenshot below, we have our sample dataset that consists of three columns in the Data sheet – Account Manager, State and State Abbreviation. The data is formatted as an Excel Table.
Search Criteria
In cell F1, we want to type in pa as the search criteria that we want to search from the Account Manager’s column.
In cell E2, we want to execute a formula using the SEARCH text function In this case. The SEARCH function requires two compulsory and 1 optional argument
=SEARCH(find_text,within_text,[start_num])
- In cell E2, execute formula: =SEARCH(F1,A2:A51)
- Click Enter. From the screenshot below, we noticed that rows six and 14 satisfied the search criteria because they have pa in the Account Manager’s names hence, the SEARCH function returned the number of characters in which “pa” was found.
Next, we want to convert the returned values that met the search criteria to return TRUE otherwise FALSE
In this case, we are going to use the ISNUMBER Function that give us TRUEs and FALSEs. In addition, we are going to use the optional IFERROR Function inside the ISNUMBER Function
- In cell E2, press F2 to put the cell in active mode
- Modify the formula as follows: =ISNUMBER(IFERROR(SEARCH(F1,A2:A51),””))
- Click Enter
FILTER FUNCTION
Next, we want to use FILTER Function to filter the dataset based on the “ISNUMBER(IFERROR(SEARCH(F1,A2:A51),””))” criteria
- In cell E2, press F2 to put the cell in active mode
- Modify the formula as follows: =FILTER(A2:A51,ISNUMBER(IFERROR(SEARCH(F1,A2:A51),””)),”No Record Found”)
- Click Enter. Note, we used “No Record Found” input value for the if_empty argument of the FILTER Function
From the above screenshot, we have the filtered list of Account Managers that has pa in their names.
Next, in the Result sheet, we want a list Data Validation in cell A2 that will fed by the filtered result from the Data sheet. Therefore,
- Select cell A2 in the Result sheet
- In the Data Tool group of the Data tab, click on Data Validation
- In the Data Validation dialogue box under Settings, select List from the Allow dropdown
- Click in the Source and then click on the Data worksheet
- Select cell E2 and include the # sign. This will return the entire list in the range and it works with Dynamic Array formulas.
- Next, click o0n the Error Alert tab
- Uncheck Show Alert as seen below
- Click OK
Next, we want to link the List Data Validation in cell A2 of the Result sheet to the formula in the cell E2 of the Data sheet. To do this:
- Click in the cell E3 in the Data sheet
- In the formula, carefully replace the F1 which represent the input value for the SEARCH find_what argument. Replace with cell A2 in the Result sheet.
We would have the syntax below
=FILTER(A2:A51,ISNUMBER(IFERROR(SEARCH(Result!A2,A2:A51),””)),”No Record Found”)
From the screenshot below, we can see that the searchable dropdown is working. When I typed ch in cell A2, we can see the list of the Account Managers that has ch in their names
Finally, we want to use the XLOOKUP Function to return the corresponding State and State Abbreviation in cells B2 and C2 of the Result sheet based on the lookup value in cell A2.
- Therefore, in cell B2, execute the formula: =XLOOKUP(A2,Data!A2:A51,Data!B2:C51,”No Record”)
- Click Enter. As expected the dynamic array functionality is working – the result spilled to cell C2 without the need to use the Fill Handle to manually drag the formula to the right. See caption below
This is how we can create searchable dropdown in Excel for the Web. See you in the next tutorial