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

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

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

Minh Hung asks: Hello Mr Oscar I have the matter to create a megaformula to categorize my list. For short […]

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

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

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. Click "Sort & Filter" button.

4. Click "Filter".

The header names now have arrows.

1. Click an arrow based on the column you want to filter.

2. Click "Text Filters".

3. Click "Contains..".

4. Type the text string you want to match.

5. Click 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.