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

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

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

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

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

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

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.

Read more articles about this topic

The following article shows you how to VLOOKUP and return multiple values, the lookup value must match the entire cell value.

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Comments(445) Filed in category: Excel, VLOOKUP and return multiple values

Search for a cell value in a dataset:

Search for a cell value in a dataset

This blog article is one out of five articles on the same subject. Search for a cell value in an […]

Comments(1) Filed in category: Excel, VLOOKUP and return multiple values

How to look for values in a column that contain two text strings:

Search for multiple text strings in multiple cells

Question: How do I search a list for two text strings and return a list with where both strings occurs? […]

Comments(17) Filed in category: Excel, Search and return multiple values

Search two columns for two text strings. The strings can be anywhere in these two columns but both text strings have to be found in different cell values or a single cell value and be on the same row to match:

Lookup with multiple criteria and display multiple search results using excel formula, part 2

Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns […]

Comments(3) Filed in category: Excel

Search for three text strings in three columns, one text string in each column. Return values in which all three cases are found:

Lookup with multiple criteria and display multiple search results using excel formula, part 3

Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]

Comments(16) Filed in category: Excel

Search for multiple text strings in multiple columns, one text string in each column. Return values in which all text strings match:

Lookup with multiple criteria and display multiple search results using excel formula, part 4

Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]

Comments(27) Filed in category: Excel, Search and return multiple values

Search for a single text string in a single column and return multiple matches.

Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Comments(46) Filed in category: Excel, Search and return multiple values

Search for a text string in a single column and return multiple corresponding values.

Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Comments(46) Filed in category: Excel, Search and return multiple values

Search for multiple text strings in a single column and return multiple corresponding values.

Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Comments(46) Filed in category: Excel, Search and return multiple values

Search for a text string in multiple columns and return corresponding values.

Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Comments(46) Filed in category: Excel, Search and return multiple values