## 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 of strings in column D was to increase to a large number e.g. 15, how would you tell excel 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?*

**Array Formula in G3:**

Change the following in order to add more conditions:

- cell reference $E$2:$E$3 if you want more conditions
- the number after the second equal sign =2 to as many conditions you have in the formula
- {1; 1} to as many conditions you have. For example, 4 conditions - {1; 1; 1; 1}

#### How to create an array formula

### Explaining formula in cell

#### Step 1 - Search for multiple strings

The SEARCH function allows you to find a string in a cell and it's character position. It also allows you to search for multiple strings in multiple cells if you arrange values in a way that works. That is why I use the TRANSPOSE function to transpose the values.

SEARCH(TRANSPOSE($E$2:$E$3), $B$3:$B$13)

becomes

SEARCH(TRANSPOSE({"B";"f"}), $B$3:$B$13)

becomes

SEARCH({"B","f"}, $B$3:$B$13)

and returns

{#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 values into boolean values

The ISNUMBER function returns TRUE if value is number and FALSE for everything else including errors.

ISNUMBER(SEARCH(TRANSPOSE($E$2:$E$3), $B$3:$B$13))

becomes

ISNUMBER({#VALUE!, 1; 1, #VALUE!; 2, #VALUE!; #VALUE!, #VALUE!; #VALUE!, #VALUE!; 1, #VALUE!; #VALUE!, 1; 1, 2; 2, #VALUE!; #VALUE!, 3; 3, 1})

and returns

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

#### Step 3 - Convert boolean values

The MMULT function can't work with boolean values so we need to convert them to their numerical equivalents.

--(ISNUMBER(SEARCH(TRANSPOSE($E$2:$E$3), $B$3:$B$13)))

becomes

--({FALSE, TRUE;TRUE, FALSE;TRUE, FALSE;FALSE, FALSE;FALSE, FALSE;TRUE, FALSE;FALSE, TRUE;TRUE, TRUE;TRUE, FALSE;FALSE, TRUE;TRUE, TRUE})

and returns

{0,1;1,0;1,0;0,0;0,0;1,0;0,1;1,1;1,0;0,1;1,1}.

#### Step 4 - Add numbers in array row-wise

MMULT(--(ISNUMBER(SEARCH(TRANSPOSE($E$2:$E$3),$B$3:$B$13))),{1;1})=2

becomes

MMULT({0,1;1,0;1,0;0,0;0,0;1,0;0,1;1,1;1,0;0,1;1,1}, {1;1})=2

becomes

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

and returns

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

#### Step 5 - Prevent duplicates in the list

The next COUNTIF function counts values based on a condition or criteria, the first argument has this cell reference: $G$2:G2. It expands as you copy the cell and paste to cells below.

(COUNTIF($G$2:G2, $B$3:$B$13)=0)

becomes

{0;0;0;0;0;0;0;0;0;0;0}=0

and returns

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

#### Step 6 - Multiply arrays

We apply AND logic if we multiply the arrays, this means both values must be TRUE in order to return TRUE.

(COUNTIF($G$2:G2, $B$3:$B$13)=0)* (MMULT(--(ISNUMBER(SEARCH(TRANSPOSE($E$2:$E$3), $B$3:$B$13))), {1; 1})=2)

becomes

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

and returns

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

#### Step 6 - Divide 1 with array

The LOOKUP function ignores error values. Divide 1 with zero and we get #DIV/0! error.

1/((COUNTIF($G$2:G2, $B$3:$B$13)=0)* (MMULT(--(ISNUMBER(SEARCH(TRANSPOSE($E$2:$E$3), $B$3:$B$13))), {1; 1})=2))

becomes

1/{0;0;0;0;0;0;0;1;0;0;1}

and returns

{#DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; 1; #DIV/0!; #DIV/0!; 1}.

#### Step 7 - Return values

LOOKUP(2, 1/((COUNTIF($G$2:G2, $B$3:$B$13)=0)*(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE($E$2:$E$3), $B$3:$B$13))), {1; 1})=2)), $B$3:$B$13)

becomes

LOOKUP(2, {#DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; 1; #DIV/0!; #DIV/0!; 1}, $B$3:$B$13)

becomes

LOOKUP(2, {#DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; #DIV/0!; 1; #DIV/0!; #DIV/0!; 1}, {"F"; "BAA"; "DBB"; "ADD"; "DAD"; "BDA"; "FDA"; "BFA"; "ABA"; "DAF"; "FDB"})

and returns "FDB" in cell G3.

**Get Excel *.xlsx file**

Search and display all cells that contain all search strings.xlsx

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

This article demonstrates a formula that extracts a row or record from a data table using two strings as criteria. […]

This article demonstrates formulas that let you perform partial matches based on multiple strings and return those strings if all […]

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

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

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

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

This article demonstrates a formula that searches a cell (partial match) based on values in a table and returns a […]

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

Question: How do i create a flexible search formula to search a list? Answer: The following formula let´s you search […]

This article demonstrates how to distribute values into specific ranges with possible overlapping ranges. I have written articles about filter […]

### 20 Responses to “Search and display all cells that contain multiple search strings”

### Leave a Reply to Oscar

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

Can you do this by displaying an adjacent column instead of the column that was searched?

if that is not possible then can you do this formula by having List (A2:A12) start at A3 and go to A13?

okay I figured it out! thanks and I love your site!!

Man how long have you been working with excel?... i have just 2 years and I didn't have the slightest idea (till know ) you can do this only with formulas, I usually solve this kind of issues with VBA macros.

What can you recommend me to be able to do this? review each one of the formulas and its examples , or reading a lot of excel books or what?

I have learned a lot just by starting an excel blog.

Review others formulas and reading books is a good start. Enjoy what you are doing and solutions come easily into mind.

Oscar,

Thanks again. Would you help me with replaced search string1 with replace string1 and search string2 with replace string2....etc.... thanks,

James

Great! although it's unable to correctly find the 02590 string. What could it be?

Andres,

02590?

Can you provide an example?

Hello Oscar,

My worksheet is failing not yours (blush). I put new strings on yours and it works just as I expected. Need to dig into it a bit more, he he.

Thanks for your valuable attention and congrats for your portal.

- Andres.

Hello Oscar,

Here I have an example (https://www.yourfilelink.com/get.php?fid=830240) for the functions of this page. As you can see in this example I am telling the spreadsheet to find what is in cell A3 but it show the content for row 3775 instead. I used hardcoded ranges aswell as named ranges with no change. When I put the content of the Example.xls spreadsheet in your spreadsheet it works but refuses to work in mine, perhaps due because I am using Excel 2003 but I am not fully convinced.

Thank you!

- Andres.

Andres,

Use this array formula in cell K7:

Hi Oscar,

I need to combine row and text columns into one "text" and suppress errors. Excel complaints that I exceeded the nested formulas.

Much appreciated..

Sorry Oscar,

Forgot to mention, only one search string is required, and may contain letters and numbers.

Best regards.

Carl,

combine row and text columns into one "text" and suppress errorsCan you provide an example?

I have two types of cells in the worksheet. Some are with green background and some are with plain white background. Some of these cells have a string of syntax in regular expression "ABC".

Each cell could have 1 or more strings according to regular expression above separated by ","(comma).

Could experts guide me on a formula

How to count the number of "ABC" in a every green/white cells to have an over all total in a given range.

For example, if cell 1 have 3 strings, cell 2 has 5 strings. The total is 8

G8 work bro, can we get the results in column wise rather then rows ??

It works horizontally as well. Use the same formulas as above but enter it in cell H2.

Then copy cell H2 and paste it to I2 and as far as needed.

=LOOKUP(2, 1/((COUNTIF(

$G$2:G2, $B$3:$B$13)=0)*(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE($E$2:$E$3), $B$3:$B$13))), {1; 1})=2)), $B$3:$B$13)The bolded cell reference above is important in order to get values horizontally: $G$2:G2

You need to change it so it points to the cell to the left of the current cell. Example, if you are going to enter the formula in cell L5 then change the cell reference to $K$5:K5.

Hi,

I have a list of movies into a column, and I want to list all cells that contains a specific word/s.

It is similar than doing a filter and looking for some chain "text", and I want to know and list all the cells that match that chain text.

Is that possible?

Joaquin,

The easiest way to list all cells containing "text" would be to apply a filter.

1. Select any cell in your data set.

2. Go to tab "Home".

3. Press with left mouse button on "Sort & Filter" button.

4. Press with left mouse button on "Filter".

The header names now have arrows.

1. Press with left mouse button on an arrow based on the column you want to filter.

2. Press with left mouse button on "Text Filters".

3. Press with left mouse button on "Contains..".

4. Type the text string you want to match.

5. Press with left mouse button on ok button.

The data is now filtered.

Is there a way to change TRANSPOSE($E$2:$E$3) from a column of data to a row?

I've tried $D$2:$E$2 and neither work in the cell but in the Function Arguments box it shows the expected value.

I think it is an issue with circular references but Excel can't show the problem.