Search for multiple text strings in multiple cells in excel
Question: How do I search a list for two text strings and return a list with where both strings occurs?
Answer: Here is a formula (F2:F4) that creates a list with text values where both strings occur and is not case-sensitive.
Array formula in F2:F4
=INDEX(Text_col, SMALL(IF(FREQUENCY(IF(ISERR(SEARCH(TRANSPOSE(Search_col), Text_col)), "", ROW(Text_col)-MIN(ROW(Text_col))+1), ROW(Text_col)-MIN(ROW(Text_col))+1)=ROWS(Search_col), ROW(Text_col)-MIN(ROW(Text_col))+1, ""), ROW(1:1))) + CTRL + SHIFT + ENTER copied down as far as needed.
How to implement this formula to your excel worksheet
Change named ranges
Named ranges
Text_col (A2:A12)
Search_col (D2:D3)
What is named ranges?
How to increase the number of search strings
Change the cell reference for the named range Search_col.
Download excel example file
Search-for-multiple-strings-in-multiple-cells.xls
(Excel 97-2003 Workbook *.xls)
Functions in this article:
IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
ROW(reference) Returns the rownumber of a reference
ROWS(array) returns the number of rows in a reference or an array
SMALL(array,k) Returns the k-th smallest row number in this data set.
SEARCH() Returns the number of the character at which a specific character or text string is first found, reading left to right (not case sensitive)
FREQUENCY(data_array, bins_array)
Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than Bins_array.
TRANSPOSE(array)
Converts a vertical range to a horizontal range, or vice versa.
Read more articles about this topic:
- Using array formula to look up multiple values in a list
- How to return multiple values using vlookup in excel
Read more Excel – Search/Lookup
Related posts:
- Search for multiple text strings in multiple cells and use in data validation in excel
- Search for multiple text strings in multiple cells in excel, part 2
- Search and display all cells that contain all search strings in excel
- Lookup with multiple criteria and display multiple search results using excel formula, part 3
- Lookup with multiple criteria and display multiple search results using excel formula, part 4
- Lookup with multiple criteria and display multiple search results using excel formula, part 2
- Search for a text string in an excel table
- Lookup with multiple criteria and display multiple search results using excel formula
- Lookup values in a range using two or more criteria and return multiple matches in excel
- Search and display a range of values in excel



July 13th, 2009 at 2:12 pm
If the list of strings in column D was to increase to a large number e.g. 15, how would you tell excell to select the range of strings, so that you don't have to select each string "SEARCH($D$3" in the search parameter, as it seems is the case at the moment?
July 14th, 2009 at 10:40 pm
Interesting question! I will look into this as soon as possible. Thanks for commenting!
July 15th, 2009 at 10:56 pm
See this blog post: Search for multiple text strings in multiple cells in excel, part 2
August 24th, 2009 at 10:56 pm
Jerome, see this blog post: http://www.get-digital-help.com/2009/08/24/search-and-display-all-cells-that-contain-all-search-strings-in-excel/
November 24th, 2009 at 7:34 am
can you please modify the function (f2:f11) to display the text strings in CELL G.. disregard the ROW NUMBER Display thnx
November 24th, 2009 at 9:10 am
the function will be in data validation.. and it will display 2 outputs in the list
November 24th, 2009 at 10:02 pm
PIPO,
I have now updated this blog post. The array formula is now easier to work with. Only copy the formula to your worksheet and create the named ranges.
Thanks for bringing this post to my attention.
November 25th, 2009 at 3:45 am
Oscar,
Thank you sir, but if i put the array formula directly in the data validation list.. it only display "BFA" in the list.. i don't want to create a name range for the search result to display in data validation... thanks again in advance
November 25th, 2009 at 11:43 pm
PIPO,
I don´t know how to use an array formula in a data validation list. It seems to only "accept" a range of values.
Very interesting question, maybe someone else has an answer?
But I managed to create a "custom" data validation, see this post: http://www.get-digital-help.com/2009/11/25/search-for-multiple-text-strings-in-multiple-cells-and-use-in-data-validation-in-excel/