Question: 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

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

Question from this blog post:  Search for multiple text strings in multiple cells in excel

Answer: I can´t create a data validation "drop down" list without using a "helper" column. But I can use a "custom" validation criteria.

Here is how to do it in excel 2007:

1. Click "Data" in the ribbon
2. Select a cell (A16 in this example)
3. Click "Data validation"
4. Select "Custom" in the "Allow:" drop down list
5. Type this formula in "Formula:" field=SUM(IF(A16=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), Text_col, ), 1, 0))>0
6. Click OK

A is not validated because it is not in the search results (F2:F3).

Named ranges

Text_col (A2:A12)
Search_col (D2:D3)
What is named ranges?

Download excel example file

Search-for-multiple-strings-in-multiple-cells2.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

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

ROW(reference) Returns the rownumber of a reference

ROWS(array) returns the number of rows in a reference or an array

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)

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.

TRANSPOSE(array)
Converts a vertical range to a horizontal range, or vice versa.

Related posts:

Search for multiple text strings in multiple cells in excel

Search for multiple text strings in multiple cells in excel, part 2

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

Dependent data validation lists in multiple rows

Add values to a data validation list (vba)