Practical Excel skills sharing: making efficient search-style drop-down menus

In the previous article "Sharing practical Excel skills: Making dynamic Gantt charts", we learned how to make dynamic Gantt charts in Excel. Today we are going to talk about Excel data drop-down menus and introduce how to make efficient search-style drop-down menus. Come and take a look!

# At work, we often use Excel data validation to create drop-down menus to standardize data input and save data entry time. But when there are many data options in the drop-down menu, it will be difficult to find the data. For example, in the picture below, there are too many data options in the drop-down menu. It is time-consuming to "find" the required data items by dragging the scroll bar next to it, which directly reduces our work efficiency.

#So is there any way to solve this problem of too many options and hard to find data?

Yes, my method is the search drop-down menu!

Just like searching on Baidu, after entering the keyword, a drop-down menu will pop up to display search questions containing the keyword for selection. The effect we want to achieve is to enter keywords in the cell, and then click the drop-down menu. Only the data containing the keywords will be displayed in the menu, thereby improving data entry efficiency.

The picture below is the data source for our tutorial. Note that the data source must be sorted by keyword, either in ascending or descending order.

Select the cell range E2:E6, click the [Data] tab, click [Data Verification], and select "Settings" in the pop-up "Data Verification" dialog box. Set the verification condition in the card to "sequence".

Enter the formula in the source:

=OFFSET($A$1,MATCH(E2&"*",$A$2:$A $17,0),0,COUNTIF($A$2:$A$17,E2&"*"),1)

##Formula description:

Here we mainly use the OFFSET function to return data containing keywords. The function of the OFFSET function is to return new reference data through the given offset based on the specified reference cell.

OFFSET (reference frame, row offset, column offset, number of rows in the new reference area, number of columns in the new reference area)

• 1. The first parameter refers to cell A1 as the reference system.

• 2. The second parameter uses MATCH(E2&"*",$A$2:$A$17,0) to determine the row offset. MATCH is a search function. It searches in the area $A$2:$A$17 based on the search value E2&"*" (* is a wildcard character, representing any uncertain character). The search mode is 0 (exact search). When a keyword is entered in cell E2, this function will find the first occurrence of the data containing the keyword in the $A$2:$A$17 range.

• 3. The third parameter is 0, because our data source only has one column, column A, so the column offset is 0, which means no offset. To put it simply, the offset function takes cell A1 as a reference and does not offset horizontally, but only offsets downward.

• 4. The fourth parameter COUNTIF($A$2:$A$17,E2&"*") counts the conditions E2&"*" in the A2-A17 area, which means it contains the E2 unit The number of times the keywords in the cell appear, that is, how many rows will appear in the data validation drop-down menu.

• 5. The fifth parameter is the number of columns in the new reference area. Since there is only column A, it is 1.

As shown below: The offset function searches downward using A1 as the reference system, and uses the match function to find the first occurrence of data containing the E2 keyword "broken flowers" in A2-A17 The position is the 10th row starting from A2, and then use the countif function to find a total of 3 rows, and finally return the data of these 3 rows and 1 column in the drop-down menu.

Return to the tutorial. We enter the formula in the "Source" of the "Data Validation" dialog box and click "OK". But when we enter the keyword "broken flowers" in cell E2, a warning box will pop up immediately. Why is this?

The reason is that after we enter the keyword "broken flower", the drop-down menu obtained by the formula does not have an option containing only the word "broken flower", so an error will be reported .

As shown in the figure below, we need to select the cell range E2:E6 again and click the [Data] tab [Data Verification] button to enter the "Data Verification" dialog box and uncheck the "Error Warning" tab. [Show error warning when entering invalid data] option, and then click "OK".

Finally enter the formula =IFERROR(VLOOKUP(E2,$A$2:$B$17,2,0),"") in cell F2. Use the VLOOKUP function to find the location of the E2 value in the cell range A2-B17, and return the corresponding inventory in column 2 (that is, column B). 0 represents an accurate search. When an error value cannot be found and an error value is returned, use the IFERROR function to convert the error value to null.

At this point, the search drop-down menu is completed!

Search-style drop-down menus can exponentially improve data entry efficiency, especially when there are many drop-down menu options. Classmate, quickly open your excel and do some operations.

Related learning recommendations: excel tutorial

The above is the detailed content of Practical Excel skills sharing: making efficient search-style drop-down menus. For more information, please follow other related articles on the PHP Chinese website!