How to Extract Rows Containing a Search Term in Excel
In Excel, you can extract and display rows where specific cells contain a search term. This functionality helps efficiently filter relevant rows from large datasets. This article explains how to search for a term within a range using partial matches and display the corresponding rows in another range.
Example
Suppose you have the following dataset:
| A | B | C | |
|---|---|---|---|
| 1 | Apple | Orange | Grape |
| 2 | Strawberry | Pear | Watermelon |
| 3 | Cherry | Melon | Banana |
| 4 | Peach | Kiwi | Apple |
| 5 | Apple |
When the search term “Apple” (entered in cell A5) is searched within the range A1:C4, the rows containing “Apple” will be displayed in A6:C6 onward.
Method: Using the FILTER Function
The FILTER function in Excel makes it easy to extract such rows.
Steps
- Enter the search term in cell A5 (e.g., “Apple”).
- In A6, enter the following formula:
=FILTER(A1:C4, BYROW(A1:C4, LAMBDA(row, SUMPRODUCT(–ISNUMBER(SEARCH(A5, row)))>0))) - Press Enter to display rows containing “Apple” starting from A6:C6.
Formula Explanation
- FILTER(A1:C4, …): Extracts rows from the range A1:C4 based on the condition.
- BYROW(…): Evaluates the condition row by row.
- LAMBDA(row, …): A custom function that checks if each row meets the condition.
- SEARCH(A5, row): Searches for the term in A5 within each cell in the row (partial match).
- SUMPRODUCT(–ISNUMBER(…))>0: Returns TRUE if the term is found in at least one cell in the row.
Result
When “Apple” is entered as the search term, the output will be:
| A | B | C | |
|---|---|---|---|
| 6 | Apple | Orange | Grape |
| 7 | Peach | Kiwi | Apple |
Notes
- The FILTER function is available in Excel 365 and Excel 2021.
- If no matches are found, the #CALC! error will appear. To handle this, you can add a default value to the FILTER function (e.g., =FILTER(…, …, “No Match”)).
- Partial match searches are case-insensitive.
- To avoid displaying #CALC! errors, use =IFERROR(FILTER(…),”No Data”).
Conclusion
Using the FILTER function, you can easily extract and display rows containing a search term in Excel. This technique is highly useful for extracting relevant data from large datasets efficiently.