## Search and display all cells that contain all search strings in excel

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

Now I finally have an answer*.*

**Array Formula in G2:**

**How to create an array formula**

**Named ranges**

List (A2:A12)

Search_strings (D2:D3) *Change this range if you want to include more search strings*

What is named ranges?

**How to implement array formula to your workbook**

Change named ranges.

**Download excel example file.**

Search and display all cells that contain all search strings.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

**ROW(**reference**)**

Returns the rownumber of a reference

**ISERROR(**value)

Checks whether a value is an error and returns TRUE or FALSE

**SMALL(**array,k**)**

Returns the k-th smallest row 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)

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

**TRANSPOSE(**array**)**

Converts a vertical range to a horizontal range, or vice versa.

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

*.*

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 display multiple unique search results (array formula)

RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" […]Split search value using delimiter and search for each substring

Anil asks: I have A1(anil singh raj) It can be anything Like A1(singh raj anil) I want return value in […]You can click on headers to sort table for easy finding.

### 15 Responses to “Search and display all cells that contain all search strings in excel”

### Leave a Reply

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

<code>your formula</code>

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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