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:

1. Search for multiple text strings in multiple cells in excel
2. Search for multiple text strings in multiple cells in excel, part 2
3. Search and display all cells that contain all search strings in excel
4. Lookup with multiple criteria and display multiple search results using excel formula, part 3
5. Lookup with multiple criteria and display multiple search results using excel formula, part 2
6. Lookup with multiple criteria and display multiple search results using excel formula, part 4
7. Lookup with multiple criteria and display multiple search results using excel formula
8. Search for a text string in an excel table
9. Search for a cell value in an excel table