Search for multiple text strings in multiple cells in excel

Popular categories

Dashboard reporting with excel This e-book teaches you how to create your own Excel dashboard reports, starting from scratch. Learn how to create mini-charts, how to use Excel's Camera tool, how to set up Excel databases, and a lot more.

Excel Worksheet-Formula Add-Ins allows worksheet formulas to change chart settings. For the first time ever, your formulas can create traffic-light charts, highlight chart elements, assign number formats, and much more.

Get Digital Help > Excel > Search/Lookup > Search for multiple text strings in multiple cells in excel

← Previous post -

Filed in Excel, Search/Lookup on Apr.21, 2009. Email This article to a Friend

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 cell F2

=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.

Copy cell F2 and paste down as far as needed.

Alternative array formula in cell F2

=INDEX(Text_col, SMALL(IF(ISERR(SEARCH($D$2, Text_col)*SEARCH($D$3, Text_col)), "", ROW(Text_col)-MIN(ROW(Text_col))+1), ROW(1:1))) + CTRL + SHIFT + ENTER.

Copy cell F2 and paste down as far as needed.

Explaining array formula in cell F2

Step 1 - Find cells containing both strings

=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)))

SEARCH(find_text, within_text, [start_num]) returns the number of the character at which a specific character or text string is first found, reading left to right (not case sensitive)

SEARCH(TRANSPOSE(Search_col), Text_col))

becomes

SEARCH(TRANSPOSE("B"; "f"), {"F";"BAA";"DBB";"ADD";"DAD";"BDA";"FDA";"BFA";"ABA";"DAF";"FDB"}))

becomes

SEARCH({"B", "f"}, {"F";"BAA";"DBB";"ADD";"DAD";"BDA";"FDA";"BFA";"ABA";"DAF";"FDB"}))

becomes

{#VALUE!, 1;1, #VALUE!;2, #VALUE!;#VALUE!, #VALUE!;#VALUE!, #VALUE!;1, #VALUE!;#VALUE!, 1;1, 2;2, #VALUE!;#VALUE!, 3;3, 1}

Step 2 - Convert numbers to row numbers

=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)))

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

IF(ISERR(SEARCH(TRANSPOSE(Search_col), Text_col)), "", ROW(Text_col)-MIN(ROW(Text_col))+1)

becomes

IF(ISERR({#VALUE!, 1;1, #VALUE!;2, #VALUE!;#VALUE!, #VALUE!;#VALUE!, #VALUE!;1, #VALUE!;#VALUE!, 1;1, 2;2, #VALUE!;#VALUE!, 3;3, 1}, "", ROW(Text_col)-MIN(ROW(Text_col))+1)

becomes

IF(ISERR({#VALUE!, 1;1, #VALUE!;2, #VALUE!;#VALUE!, #VALUE!;#VALUE!, #VALUE!;1, #VALUE!;#VALUE!, 1;1, 2;2, #VALUE!;#VALUE!, 3;3, 1}, "", ROW(A2:A12)-MIN(ROW(A2:A12))+1)

becomes

IF(ISERR({#VALUE!, 1;1, #VALUE!;2, #VALUE!;#VALUE!, #VALUE!;#VALUE!, #VALUE!;1, #VALUE!;#VALUE!, 1;1, 2;2, #VALUE!;#VALUE!, 3;3, 1}, "", {2;3;4;5;6;7;8;9;10;11;12}-MIN({2;3;4;5;6;7;8;9;10;11;12})+1)

becomes

IF(ISERR({#VALUE!, 1;1, #VALUE!;2, #VALUE!;#VALUE!, #VALUE!;#VALUE!, #VALUE!;1, #VALUE!;#VALUE!, 1;1, 2;2, #VALUE!;#VALUE!, 3;3, 1}, "", {2;3;4;5;6;7;8;9;10;11;12}-2+1)

becomes

IF(ISERR({#VALUE!, 1;1, #VALUE!;2, #VALUE!;#VALUE!, #VALUE!;#VALUE!, #VALUE!;1, #VALUE!;#VALUE!, 1;1, 2;2, #VALUE!;#VALUE!, 3;3, 1}, "", {1;2;3;4;5;6;7;8;9;10;11})

becomes

IF(ISERR({#VALUE!, 1;1, #VALUE!;2, #VALUE!;#VALUE!, #VALUE!;#VALUE!, #VALUE!;1, #VALUE!;#VALUE!, 1;1, 2;2, #VALUE!;#VALUE!, 3;3, 1}, "", {1;2;3;4;5;6;7;8;9;10;11})

becomes

IF({TRUE, FALSE;FALSE, TRUE;FALSE, TRUE;TRUE, TRUE;TRUE, TRUE;FALSE, TRUE;TRUE, FALSE;FALSE, FALSE;FALSE, TRUE;TRUE, FALSE;FALSE, FALSE}, "", {1;2;3;4;5;6;7;8;9;10;11})

and returns

{"", 1;2, "";3, "";"", "";"", "";6, "";"", 7;8, 8;9, "";"", 10;11, 11}

Step 3 - Calculates how often values occur within a range of values

=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)))

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.

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)

becomes

FREQUENCY{"", 1;2, "";3, "";"", "";"", "";6, "";"", 7;8, 8;9, "";"", 10;11, 11}, ROW(Text_col)-MIN(ROW(Text_col))+1)

becomes

FREQUENCY{"", 1;2, "";3, "";"", "";"", "";6, "";"", 7;8, 8;9, "";"", 10;11, 11}, {1;2;3;4;5;6;7;8;9;10;11})

returns this array:

{1;1;1;0;0;1;1;2;1;1;2;0}

Step 4 - Convert array values equal to the number of strings, into row numbers

=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)))

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

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, "")

becomes

IF({1;1;1;0;0;1;1;2;1;1;2;0}=ROWS(Search_col), ROW(Text_col)-MIN(ROW(Text_col))+1, "")

becomes

IF({1;1;1;0;0;1;1;2;1;1;2;0}=ROWS(D2:D3), ROW(A2:A12)-MIN(ROW(A2:A12))+1, "")

becomes

IF({1;1;1;0;0;1;1;2;1;1;2;0}=2, {1;2;3;4;5;6;7;8;9;10;11}, "")

becomes

IF({1;1;1;0;0;1;1;2;1;1;2;0}=2, {1;2;3;4;5;6;7;8;9;10;11}, "")

becomes

IF({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}, {1;2;3;4;5;6;7;8;9;10;11}, "")

returns this array

{"";"";"";"";"";"";"";8;"";"";11;""}

Step 4 - Return the k-th smallest number

SMALL(array,k) returns the k-th smallest number in this data set.

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))

becomes

SMALL({"";"";"";"";"";"";"";8;"";"";11;""}, ROW(1:1))

becomes

SMALL({"";"";"";"";"";"";"";8;"";"";11;""}, 1) and returns 8.

Step 5 - Return a value of the cell at the intersection of a particular row and column

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

becomes

=INDEX(Text_col, 8 )

becomes

=INDEX({"F";"BAA";"DBB";"ADD";"DAD";"BDA";"FDA";"BFA";"ABA";"DAF";"FDB"}, 8 )

and returns BFA in cell F2.

Explaining alternative array formula in cell F2

=INDEX(Text_col, SMALL(IF(ISERR(SEARCH($D$2, Text_col)*SEARCH($D$3, Text_col)), "", ROW(Text_col)-MIN(ROW(Text_col))+1), ROW(1:1)))

Step 1 - Find cells containing string 1

=INDEX(Text_col, SMALL(IF(ISERR(SEARCH($D$2, Text_col)*SEARCH($D$3, Text_col)), "", ROW(Text_col)-MIN(ROW(Text_col))+1), ROW(1:1)))

SEARCH(find_text, within_text, [start_num]) returns the number of the character at which a specific character or text string is first found, reading left to right (not case sensitive)

SEARCH($D$2, Text_col)

becomes

SEARCH("B", {"F";"BAA";"DBB";"ADD";"DAD";"BDA";"FDA";"BFA";"ABA";"DAF";"FDB"})

and returns

{#VALUE!;1;2;#VALUE!;#VALUE!;1;#VALUE!;1;2;#VALUE!;3}

Step 2 - Find cells containing string 2

=INDEX(Text_col, SMALL(IF(ISERR(SEARCH($D$2, Text_col)*SEARCH($D$3, Text_col)), "", ROW(Text_col)-MIN(ROW(Text_col))+1), ROW(1:1)))

SEARCH(find_text, within_text, [start_num]) returns the number of the character at which a specific character or text string is first found, reading left to right (not case sensitive)

SEARCH($D$3, Text_col)

becomes

SEARCH("f", {"F";"BAA";"DBB";"ADD";"DAD";"BDA";"FDA";"BFA";"ABA";"DAF";"FDB"})

and returns

{1;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;1;2;#VALUE!;3;1}

Step 3 - Find cells containing both string 1 and 2

=INDEX(Text_col, SMALL(IF(ISERR(SEARCH($D$2, Text_col)*SEARCH($D$3, Text_col)), "", ROW(Text_col)-MIN(ROW(Text_col))+1), ROW(1:1)))

ISERR(SEARCH($D$2, Text_col)*SEARCH($D$3, Text_col))

becomes

ISERR(({#VALUE!;1;2;#VALUE!;#VALUE!;1;#VALUE!;1;2;#VALUE!;3})*({1;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;1;2;#VALUE!;3;1}))

becomes

ISERR({#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;8;#VALUE!;#VALUE!;11})

and returns

{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE}

Step 4 - Convert Boolean values to corresponding row numbers

IF(ISERR(SEARCH($D$2, Text_col)*SEARCH($D$3, Text_col)), "", ROW(Text_col)-MIN(ROW(Text_col))+1)

becomes

IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE}, "", ROW(Text_col)-MIN(ROW(Text_col))+1)

becomes

IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE}, "", ROW(A2:A12)-MIN(ROW(A2:A12))+1)

becomes

IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE}, "", {2;3;4;5;6;7;8;9;10;11;12}-MIN({2;3;4;5;6;7;8;9;10;11;12})+1)

becomes

IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE}, "", {2;3;4;5;6;7;8;9;10;11;12}-2+1)

becomes

IF({TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE}, "", {1;2;3;4;5;6;7;8;9;10;11;})

and returns

{"";"";"";"";"";"";"";8;"";"";11}

Step 5 - Return the k-th smallest number

=INDEX(Text_col, SMALL(IF(ISERR(SEARCH($D$2, Text_col)*SEARCH($D$3, Text_col)), "", ROW(Text_col)-MIN(ROW(Text_col))+1), ROW(1:1)))

SMALL(array,k) returns the k-th smallest number in this data set.

SMALL(IF(ISERR(SEARCH($D$2, Text_col)*SEARCH($D$3, Text_col)), "", ROW(Text_col)-MIN(ROW(Text_col))+1), ROW(1:1))

becomes

SMALL({"";"";"";"";"";"";"";8;"";"";11}, ROW(1:1))

becomes

SMALL({"";"";"";"";"";"";"";8;"";"";11}, 1) and returns 8.

Step 6 - Return a value at the intersection of a particular row and column

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

=INDEX(Text_col, SMALL(IF(ISERR(SEARCH($D$2, Text_col)*SEARCH($D$3, Text_col)), "", ROW(Text_col)-MIN(ROW(Text_col))+1), ROW(1:1)))

becomes

=INDEX(Text_col, 8)

becomes

=INDEX({"F";"BAA";"DBB";"ADD";"DAD";"BDA";"FDA";"BFA";"ABA";"DAF";"FDB"}, 8) and returns BFA in cell F2.

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 row number of a reference

ROWS(array)
Returns the number of rows in a reference or an array

SMALL(array,k)
Returns the k-th smallest 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.

Recommended categories

Comments (15)

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

Jerome says:
July 13, 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?
Reply
admin says:
July 14, 2009 at 10:40 pm
Interesting question! I will look into this as soon as possible. Thanks for commenting!
Reply
admin says:
July 15, 2009 at 10:56 pm
See this blog post: Search for multiple text strings in multiple cells in excel, part 2
Reply
admin says:
August 24, 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/
Reply
PIPO says:
November 24, 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
Reply
PIPO says:
November 24, 2009 at 9:10 am
the function will be in data validation.. and it will display 2 outputs in the list
Reply
Oscar says:
November 24, 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.
Reply
PIPO says:
November 25, 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
Reply
Oscar says:
November 25, 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/
Reply
James says:
October 19, 2010 at 6:42 pm
Oscar,
Thanks you, it's a very helpful website. Would you able to search for those text strings (Search_Strings) contained in each cell then show? (cell contained both D2:D3 then show). Thanks,
Reply
Oscar says:
October 21, 2010 at 8:57 pm
James, can you explain in greater detail? I don´t understand.
Reply
Jeremy says:
January 21, 2011 at 3:25 am
Oscar, how can I use the Text_col on a different sheet in the same worksheet?
Reply
Oscar says:
January 21, 2011 at 10:42 pm
Text_col is a named range. Select a new range on a different sheet.
Reply
Oscar says:
September 5, 2011 at 1:14 pm
Oscar,
Rather than display the values could you collate them as part of a sum?
I have a a sheet that i could use a little help with, is thee any chance you could give me some advise?
Thanks
Daniel.
Reply
Oscar says:
September 7, 2011 at 9:45 am
Daniel,
Array formula in cell E10:
=SUM(IF(ISERR(SEARCH($E$2, Text_col)*SEARCH($E$3, Text_col)), "", $B$2:$B$12))
Download excel file
search and sum.xls
Reply