Detailed explanation of extracting data by keyword in excel

This article brings you relevant knowledge about excel, which mainly organizes the related issues of extracting data by keywords. Let’s take a look at it together. I hope it will be helpful to everyone.

Related learning recommendations: excel tutorial

Today we share a common data extraction problem-extracting data by keywords.

As shown in the figure below, it is a partial menu of an internal canteen of an organization. We need to use the keyword in cell E2 to extract all records that contain the keyword in the dish name.

Method 1 Function formula

G2 Enter the following array formula, hold down the Shift and Ctrl keys, and press Enter. Then copy the formula to the right and down.

=IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(FIND($E$2,$A$2:$A$85))),ROW($2:$85)),ROW(A1)) ),””)

The extraction results are as follows:

The formula roughly means, use the FIND function to query the keyword in the data area, if the key is included If the word is used, the number representing the position is returned, otherwise an error value is returned.

Then use the ISNUMBER function to determine whether the result of the FIND function is a numerical value, which is equivalent to determining whether it contains keywords.

Next use the IF function to return the corresponding line number if the keyword is included.

Then use the SMALL function to extract the row numbers containing the keywords from small to large, and use the INDEX function to return the content of the corresponding position in column A.

Is it complicated? Are you confused? Ha ha ha ha.

Method 2 Using Office 365

If you are using Office 365, the formula will be much simpler:

Enter the following formula in cell G2, press Just press Enter.

=FILTER(A2:B85,ISNUMBER(FIND(E2,A2:A85)))

The FILTER function is unique to the 365 version. The function is to filter records according to conditions. The first parameter is the data area to be processed, and the second parameter is the specified filtering condition. For the filtering condition here, we still use the form of ISNUMBER FIND to determine whether it contains keywords.

The advantage of using formulas is that when the keywords change, the results can be automatically updated. However, when the first formula processes a large amount of data, Excel will freeze if you are not careful.

Related learning recommendations: excel tutorial

The above is the detailed content of Detailed explanation of extracting data by keyword in excel. For more information, please follow other related articles on the PHP Chinese website!