## Search for multiple text strings in column – AND logic

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

*+ CTRL + SHIFT + ENTER.*

Copy cell F2 and paste down as far as needed.

### Alternative array formula in cell F2

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

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 […]

Lookup with multiple criteria and return multiple search results

Question: How do I search a list containing First name column and a last name column? I want to search […]

Search and display all cells that contain multiple search strings

Jerome asks, in this blog post Search for multiple text strings in multiple cells in excel : If the list […]

Search columns for a string and return records – AND logic

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

Return multiple matches with wildcard vlookup

Mr.Excel had a "challenge of the month" June/July 2008 about Wildcard VLOOKUP: "The data in column A contains a series […]

Search each column for a string each and return multiple records – OR logic

RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" […]

Wildcard lookups and include or exclude criteria

This article demonstrates three different ways to filter a data set if a value contains a specific string and if […]

Row is returned if both search strings are found on the same row

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

This article demonstrates a formula that extracts items based on two conditions. The first condition (Location) is used to find […]

### 18 Responses to “Search for multiple text strings in column – AND logic”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

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?

Interesting question! I will look into this as soon as possible. Thanks for commenting!

See this blog post: Search for multiple text strings in multiple cells in excel, part 2

Jerome, see this blog post: https://www.get-digital-help.com/2009/08/24/search-and-display-all-cells-that-contain-all-search-strings-in-excel/

can you please modify the function (f2:f11) to display the text strings in CELL G.. disregard the ROW NUMBER Display thnx

the function will be in data validation.. and it will display 2 outputs in the list

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.

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

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: https://www.get-digital-help.com/2009/11/25/search-for-multiple-text-strings-in-multiple-cells-and-use-in-data-validation-in-excel/

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,

James, can you explain in greater detail? I don´t understand.

Oscar, how can I use the Text_col on a different sheet in the same worksheet?

Text_col is a named range. Select a new range on a different sheet.

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.

Daniel,

Array formula in cell E10:Download excel filesearch and sum.xls

Oscar ,

Thank you, it's a very helpful website . I need your help in excel search . I have two Columns Cola with more than 10,000 records and Column B with 500 records. I need to show all the rows of Column A which contains case sensitive match of any records from column B.

I used the Alternative formula. When I copy down the formula, it's giving me error: #NUM!

what if I had to print the adjacent column value, consider B is the named range(Text_col), but I want the value in column A, how should I tweak the code?