Jerome asks, in this blog post Search for multiple text strings in multiple cells in excel :
Formula in F2:
You can change the cell reference $D$2:$D$3 to any size, as long it is referencing a single column.
How to create an array formula
- Select cell F2
- Paste formula to formula bar
- Press and hold CTRL + SHIFT
- Press Enter
- Release all keys
Download excel *.xlsx file
(Excel 97-2003 Workbook *.xls)
Functions in this article:
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
Counts the number of cells in a range that contain numbers
ROW(reference) Returns the rownumber of a reference
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)
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.