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
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:
Returns the rownumber of a reference
Checks whether a value is an error and returns TRUE or FALSE
Returns the k-th smallest row number in this data set.
Returns the number of the character at which a specific character or text string is first found, reading left to right (not case sensitive)
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
Converts a vertical range to a horizontal range, or vice versa.
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.