This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the same row.
The functions used in most of the formulas on this web page are the SEARCH and FIND functions. They return a number based on the position of a text string in a value, see cell E3 on picture above. If the text string is not found the functions return #VALUE! error.
"fox" is found at character 7 in value "a red fox", see picture below.
The SEARCH function is case-insensitive and the FIND function is case-sensitive. You can replace these functions with each other if you are looking for a case-sensitive formula or vice versa.
The magic starts when you enter the formula as an array formula, this allows you to search an entire cell range for text strings.
You can have more than two search strings if you like however they must be arranged horizontally (on the same row). If you want the search strings vertically (on the same column) use the TRANSPOSE function, like this: TRANSPOSE($F$1:$F$2)
Search for a text string in multiple columns and return adjacent values
The following picture shows two search text strings in cell range G2:H2, the array formula in cell range G4:G7 searches in both column B and C. If at least one text string is found the corresponding value in column D on the same row is returned to G4:G7.
You can have more than two search strings if you like, however, they must be arranged horizontally (on the same row). If you want the search strings vertically (on the same column) use the TRANSPOSE function, like this: TRANSPOSE($G$1:$G$2)