Search for multiple text strings in multiple cells in excel

Filed in Excel on Apr.21, 2009.

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.

9 Responses to “Search for multiple text strings in multiple cells in excel”

Jerome Says:
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?
admin Says:
July 14th, 2009 at 10:40 pm
Interesting question! I will look into this as soon as possible. Thanks for commenting!
admin Says:
July 15th, 2009 at 10:56 pm
See this blog post: Search for multiple text strings in multiple cells in excel, part 2
admin Says:
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/
PIPO Says:
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
PIPO Says:
November 24th, 2009 at 9:10 am
the function will be in data validation.. and it will display 2 outputs in the list
Oscar Says:
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.
PIPO Says:
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
Oscar Says:
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/